SQLite查询出现最小值大于最大值问题求助

你精准定位到问题核心了——确实是字符串类型的Consumption字段在执行MIN()和MAX()时，按字符字典序而非数值大小排序，才出现了'11'被判定为小于'9'的离谱结果。而SUM()和AVG()会自动尝试将字符串转成数值计算，所以平均值是正常的。

你的原始查询代码 #

SELECT 
  `Account`,
  --`CustomerName`,
  `METER_SIZE`,
  SUM(`Consumption`) AS `Total_Cons`,
  AVG(`Consumption`) AS `Avg_Cons`,
  MIN(`Consumption`) AS `Min_Cons`,
  MAX(`Consumption`) AS `Max_Cons`,
  COUNT(`Consumption`) AS `Bills`
FROM `Billing_Meterreads_2015_2017`
WHERE `CustomerClass` LIKE 'Residential' 
  AND `Account` LIKE '000000280';

异常查询结果 #

|Account   | METER_SIZE | Total_Cons | Avg_Cons  | Min_Cons  | Max_Cons  | Bills |
|----------|------------|------------|-----------|-----------|-----------|-------|
|000000280 | 3/4        | 597.0      | 7.6538    |11.00000   | 9.00000   | 78    |

解决方法：强制转换为数值类型计算 #

只需要在MIN()和MAX()函数中，用CAST()或CONVERT()把Consumption转成数值类型（比如DECIMAL，兼顾精度），让函数按数值逻辑比较大小。

修改后的查询代码

SELECT 
  `Account`,
  --`CustomerName`,
  `METER_SIZE`,
  SUM(`Consumption`) AS `Total_Cons`,
  AVG(`Consumption`) AS `Avg_Cons`,
  -- 转换为DECIMAL类型，确保数值精度
  MIN(CAST(`Consumption` AS DECIMAL(10,4))) AS `Min_Cons`,
  MAX(CAST(`Consumption` AS DECIMAL(10,4))) AS `Max_Cons`,
  COUNT(`Consumption`) AS `Bills`
FROM `Billing_Meterreads_2015_2017`
WHERE `CustomerClass` LIKE 'Residential' 
  AND `Account` LIKE '000000280';

额外优化建议 #

1. 永久修复：修改字段类型：如果业务允许，直接把Consumption字段从字符串类型（比如VARCHAR）改成DECIMAL或FLOAT，从根源避免这类问题，还能提升查询性能。
2. 验证转换逻辑：可以先跑一条测试语句，确认转换后的数值排序正常：

   SELECT 
     `Consumption`,
     CAST(`Consumption` AS DECIMAL(10,4)) AS `Num_Cons`
   FROM `Billing_Meterreads_2015_2017`
   WHERE `Account` LIKE '000000280'
   ORDER BY `Num_Cons`;
   

内容的提问来源于stack exchange，提问作者bstew85