在 Mysql 中汇总来自不同条件的行

本文介绍了在 Mysql 中汇总来自不同条件的行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

原表很大，我会简化一下:

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