温度字段INT(11)在CASE语句中未求和:

SELECT storenumber as storenumber,
  SUM((CASE temperature WHEN temperature <  0              THEN SumOfTotalPrice ELSE 0 END)) AS Under0,
  SUM((CASE temperature WHEN temperature BETWEEN  0 AND 20 THEN SumOfTotalPrice ELSE 0 END)) AS `0To20`,
  SUM((CASE temperature WHEN temperature BETWEEN 21 AND 30 THEN SumOfTotalPrice ELSE 0 END)) AS `21To30`,
  SUM((CASE temperature WHEN temperature BETWEEN 31 AND 40 THEN SumOfTotalPrice ELSE 0 END)) AS `31To40`,
  SUM((CASE temperature WHEN temperature BETWEEN 41 AND 50 THEN SumOfTotalPrice ELSE 0 END)) AS `41To50`,
  SUM((CASE temperature WHEN temperature BETWEEN 51 AND 60 THEN SumOfTotalPrice ELSE 0 END)) AS `51To60`,
  SUM((CASE temperature WHEN temperature BETWEEN 61 AND 70 THEN SumOfTotalPrice ELSE 0 END)) AS `61To70`,
  SUM((CASE temperature WHEN temperature BETWEEN 71 AND 80 THEN SumOfTotalPrice ELSE 0 END)) AS `71To80`,
  SUM((CASE temperature WHEN temperature BETWEEN 81 AND 90 THEN SumOfTotalPrice ELSE 0 END)) AS `81To90`,
  SUM((CASE temperature WHEN temperature > 90              THEN SumOfTotalPrice ELSE 0 END)) AS `Over90`
FROM `reconciled`.totalsalesbystoreandtemperature
GROUP BY storenumber


这是查询的结果:
mysql - 这个CASE语句有什么问题:它不是SUM?-LMLPHP

这是基础记录集的内容:
mysql - 这个CASE语句有什么问题:它不是SUM?-LMLPHP

最佳答案

您需要将所有CASE temperature WHEN ...部分更改为CASE WHEN ...,如下所示:

CASE temperature WHEN temperature BETWEEN 31 AND 40 THEN


改成

CASE WHEN temperature BETWEEN 31 AND 40 THEN


当前,CASE尝试将temperature列与WHEN语句中给出的逻辑测试结果进行比较。由于这些类型不同,因此您会不断得到false0的结果。

10-05 22:54
查看更多