用于在varchar字段中选择最小/最大值的Mysql查询

时间:2021-12-17 07:25:59

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')