mysql - 为每列添加一个包含总计的行

Question

我正在尝试向 SQL 选择查询添加一行作为最后一行，该查询包含总和(或任何其他操作，例如平均值)。在这方面它类似于 GROUP BY ... WITH ROLLUP。

首先:假设我有一个表 t，其中包含字段 ID、A、B 和 C，所有这些都是数字。此外，ID 不是唯一的，而是一个类别。我的 SELECT 查询应该计算这些数字中有多少落在指定范围内。

因此，例如，最终结果将是

(SELECT t.ID, a.ac, b.bc, c.cc FROM t
LEFT JOIN (SELECT COUNT(*) cc,ID FROM t WHERE A BETWEEN 2 AND 4 GROUP BY ID) AS a ON a.ID=t.ID 
LEFT JOIN (SELECT AVG(B) cc,ID FROM t WHERE B BETWEEN 19 AND 40 GROUP BY ID) AS b ON b.ID=t.ID 
LEFT JOIN (SELECT COUNT(*) cc,ID FROM t WHERE C BETWEEN 12 AND 14 GROUP BY ID) AS c ON a.ID=t.ID GROUP BY t.ID) 

union 

(select 'Overall',
 (SELECT COUNT(*) cc FROM t WHERE A BETWEEN 2 AND 4),
 (SELECT AVG(B) cc FROM t WHERE B BETWEEN 19 AND 40),
 (SELECT COUNT(*) cc FROM t WHERE C BETWEEN 12 AND 14) );

但是，这个解决方案并不理想，因为我需要重新陈述 A、B 和 C 的条件。我想知道是否有一种简单的方法可以只指定一次条件来完成相同的结果。

提前致谢。

Answer 1

我认为没有更简单的解决方案。但我会像这样重写您的查询:

SELECT
  t.ID,
  count(case when A between 2 and 4 then ID end),
  AVG(case when B between 19 and 40 then B end),
  COUNT(case when C between 12 and 14 then id end)
FROM t
GROUP BY ID
UNION
select
  'Overall',
  count(case when A between 2 and 4 then ID end),
  AVG(case when B between 19 and 40 then B end),
  COUNT(case when C between 12 and 14 then id end)
FROM t