原表很大,我会简化一下:
The original table is large so I will simplify it:
我的表:
CONDITION SIZE 1 10 9 10 9 10 1 20 9 20 1 20 1 30使用类似的查询
SELECT CASE WHEN CONDITION=1 THEN 'OK' ELSE 'BAD' END AS Status, SUM (CASE WHEN SIZE=10 THEN 1 ELSE 0 END) AS Small, SUM (CASE WHEN SIZE=20 THEN 1 ELSE 0 END) AS Medium, SUM (CASE WHEN SIZE=30 THEN 1 ELSE 0 END) AS Large, FROM mytable GROUP BY Status然后我们就有了这个结果
Then we have this result
Status Small Medium Large OK 1 2 1 BAD 2 1 0要获取的正确代码是什么:
What is the proper code to get:
Status Small Medium Large OK 1 2 1 BAD 2 1 0 TOTAL 3 3 1 推荐答案您可以将 WITH ROLLUP 子句添加到您的 GROUP BY 条件中,如下所示:
You can add a WITH ROLLUP clause to your GROUP BY condition like this:
SELECT CASE WHEN CONDITION=1 THEN 'OK' ELSE 'BAD' END AS Status, SUM (CASE WHEN SIZE=10 THEN 1 ELSE 0 END) AS Small, SUM (CASE WHEN SIZE=20 THEN 1 ELSE 0 END) AS Medium, SUM (CASE WHEN SIZE=30 THEN 1 ELSE 0 END) AS Large, FROM mytable GROUP BY Status WITH ROLLUP这将产生如下结果集:
Status Small Medium Large OK 1 2 1 BAD 2 1 0 [NULL] 3 3 1您需要了解状态"列中没有任何 Total 值的行为.相反,Status 列将有一个 NULL 值,指示这是进行汇总的位置.
You would need to understand the behavior that there would not be any Total value in the Status column. Instead the Status column would have a NULL value indicating that this is where the rollup is made.
有关更多信息,您可以在此处阅读文档:dev.mysql/doc/refman/5.6/en/group-by-modifiers.html
For more information you can read the documentation here: dev.mysql/doc/refman/5.6/en/group-by-modifiers.html
更多推荐
在 Mysql 中汇总来自不同条件的行
发布评论