sql - SQL 中的多个分组依据

Question

我有一张这样的 table 。

Year        Month       TenDays     Pay    
========================================
2015         8          2           12     
2016         8          1           43
2016         8          2           11
2016         9          1           22
2016         9          2           33
2016         9          3           4
2016         9          3           25

我想要一个 SQL 查询来计算 'Pay' 的总和作为 'TotalTenDays' 按 'year' 和 'Month' 和 'TenDays' 分组并按“年”和“月”计算“Pay”的总和作为“TotalMonth”组。

我可以使用“union all”来做到这一点，但我正在寻找一种不使用 union 和“with cte as()”的方法。可能吗？

预期的表格必须是这样的:

Year        Month       TenDays    TotalTenDays   TotalMonth
====================================================================
2015         8          2          12             12
2016         8          1          43             54
2016         8          2          11             54
2016         9          1          22             84
2016         9          2          33             84
2016         9          3          29             84

Answer 1

答案取决于数据库方言。

前 4 列是标准 SQL GROUP BY 逻辑，即 GROUP BY Year, Month, TenDays with a SUM(Pay) AS TotalTenDays 结果列。

TotalMonth 列最好通过使用 OVER 子句的窗口化函数来完成，但前提是 SQL 方言支持它。

例如对于 SQL Server，您可以这样做:

SELECT Year, Month, TenDays
     , SUM(Pay) AS TotalTenDays
     , SUM(SUM(Pay)) OVER (PARTITION BY Year, Month) AS TotalMonth
  FROM MyTable
 GROUP BY Year, Month, TenDays
 ORDER BY Year, Month, TenDays

参见 SQL Fiddle用于使用 MS SQL Server 2017 运行查询。

---

如果 SQL 方言不支持窗口函数，则建议 comment by Jonathan Leffler是一个很好的选择:

You need to create two sub-queries that do the aggregations, and then join the results of those two sub-queries. Each sub-query will have a different GROUP BY clause.

SELECT a.Year, a.Month, a.TenDays, a.TotalTenDays, b.TotalMonth
  FROM ( SELECT Year, Month, TenDays
              , SUM(Pay) AS TotalTenDays
           FROM MyTable
          GROUP BY Year, Month, TenDays
       ) a
  JOIN ( SELECT Year, Month
              , SUM(Pay) AS TotalMonth
           FROM MyTable
          GROUP BY Year, Month
       ) b ON b.Year = a.Year
          AND b.Month = a.Month
 ORDER BY a.Year, a.Month, a.TenDays

参见 SQL Fiddle用于使用 MySQL 5.6 运行查询。