在 Mysql 中汇总来自不同条件的行 [英] Sum rows from different conditions in 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.

有关更多信息，您可以在此处阅读文档:http://dev.mysql.com/doc/refman/5.6/en/group-by-modifiers.html

For more information you can read the documentation here: http://dev.mysql.com/doc/refman/5.6/en/group-by-modifiers.html

这篇关于在 Mysql 中汇总来自不同条件的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！