有时候我们需要从一个表中根据条件取出不同的数量,当做不同的字段展示,下面这个语句可以满足
如下要求:
SELECT SUM(CASE WHEN STATE IS NOT NULL THEN 1 ELSE 0 END) AS TOTAL,
SUM(CASE WHEN STATE = '1' THEN 1 ELSE 0 END) AS TOTAL2,SUM(CASE WHEN STATE = '2' THEN 1 ELSE 0 END) AS TOTAL3,
SUM(CASE WHEN STATE = '3' THEN 1 ELSE 0 END) AS TOTAL4
FROM TABLE WHERE COLUMN = '123'