我有一个SQL查询,即时通讯使用SUM
:
SELECT
SUM(CASE WHEN `ID_Event1` != 0 THEN 1 ELSE 0 END) AS count1,
SUM(CASE WHEN `ID_Event2` != 0 THEN 1 ELSE 0 END) AS count2,
SUM(CASE WHEN `ID_Event3` != 0 THEN 1 ELSE 0 END) AS count3,
SUM(CASE WHEN `ID_Event4` != 0 THEN 1 ELSE 0 END) AS count4,
SUM(CASE WHEN `ID_Event5` != 0 THEN 1 ELSE 0 END) AS count5
FROM `ec_calendar` WHERE Year(`Day`)=2015 AND Month(`Day`)=6
结果是:
但是我想要SUM
这些专栏。预期结果是17(3 + 4 + 3 + 3 + 4)。
有什么解决办法吗?
然后使用+
:
SELECT (SUM(CASE WHEN `ID_Event1` != 0 THEN 1 ELSE 0 END) +
SUM(CASE WHEN `ID_Event2` != 0 THEN 1 ELSE 0 END) +
SUM(CASE WHEN `ID_Event3` != 0 THEN 1 ELSE 0 END) +
SUM(CASE WHEN `ID_Event4` != 0 THEN 1 ELSE 0 END) +
SUM(CASE WHEN `ID_Event5` != 0 THEN 1 ELSE 0 END)
) as cnt
FROM `ec_calendar`
WHERE day >= '2015-06-01' and day < '2015-07-01'
请注意,我更改了该WHERE
子句。如果您在上建立索引,则此方法会更有效day
。
MySQL将布尔值视为整数,因此您可以将其写为:
SELECT (SUM(ID_Event1 <> 0) + SUM(ID_Event2 <> 0) +
SUM(ID_Event3 <> 0) + SUM(ID_Event4 <> 0) +
SUM(ID_Event5 <> 0)
) as cnt
FROM `ec_calendar`
WHERE day >= '2015-06-01' and day < '2015-07-01'
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句