So I have a varchar column which is supposed to store product prices( don't ask me how I ended up with that, but now I have no option of changing this...sigh ), It can also be blank, or contain one of the texts(literally) "null","OUT" where both represents a price of 0. What is the best and most efficient way to find the MIN and MAX value of this column?
所以我有一个varchar列应该存储产品价格（不要问我怎么结果，但现在我没有选择改变这个...叹息），它也可以是空白的，或者包含一个文本（字面意思）“null”，“OUT”，其中两者代表0的价格。找到此列的MIN和MAX值的最佳和最有效的方法是什么？
PS: I am open to php/mysql hybrid solutions, cause I need the most optimized and efficient way for this. This table is really huge...
PS：我对php / mysql混合解决方案持开放态度，因为我需要最优化和最有效的方法。这张桌真的很大......
1 个解决方案
#1
3
Something like this should work and be reasonably efficient. It's not going to be fast in any case, though.
这样的事情应该起作用并且合理有效。不过，它在任何情况下都不会很快。
SELECT
MIN(CAST(price AS DECIMAL(8,2))) as price_min,
MAX(CAST(price AS DECIMAL(8,2))) as price_max
FROM products
WHERE price NOT IN('', 'null', 'OUT')
After testing this, I noticed that apparently the casting is done automatically, so you can just do:
测试完之后，我注意到显然铸件是自动完成的，所以你可以这样做：
SELECT
MIN(price) as price_min,
MAX(price) as price_max
FROM products
WHERE price NOT IN('', 'null', 'OUT')
#1
3
Something like this should work and be reasonably efficient. It's not going to be fast in any case, though.
这样的事情应该起作用并且合理有效。不过，它在任何情况下都不会很快。
SELECT
MIN(CAST(price AS DECIMAL(8,2))) as price_min,
MAX(CAST(price AS DECIMAL(8,2))) as price_max
FROM products
WHERE price NOT IN('', 'null', 'OUT')
After testing this, I noticed that apparently the casting is done automatically, so you can just do:
测试完之后，我注意到显然铸件是自动完成的，所以你可以这样做：
SELECT
MIN(price) as price_min,
MAX(price) as price_max
FROM products
WHERE price NOT IN('', 'null', 'OUT')