如何在MySQL中实现分组数据的州级TeamCount小计与总计？

没问题，MySQL完全支持你要的这种按州添加小计、同时生成总计的需求，咱们可以用GROUP BY ... WITH ROLLUP语法来实现，这是最简洁高效的方式。

修改后的查询语句 #

SELECT
  CASE
    -- 识别总计行，显示'Total'
    WHEN GROUPING(tr.teamUserID) = 1 AND GROUPING(tu.schoolState) = 1 THEN 'Total'
    -- 识别州小计行，显示如'CA Subtotal'
    WHEN GROUPING(tr.teamUserID) = 1 THEN CONCAT(tu.schoolState, ' Subtotal')
    -- 明细行显示实际的Team Count
    ELSE CAST(COUNT(tr.teamMemberID) AS CHAR)
  END AS 'Team Count',
  CASE
    -- 总计行的State列显示'All States'
    WHEN GROUPING(tu.schoolState) = 1 THEN 'All States'
    ELSE tu.schoolState
  END AS 'State',
  CASE
    -- 小计和总计行的TeamTitle留空
    WHEN GROUPING(tr.teamUserID) = 1 THEN ''
    ELSE tu.schoolCity
  END AS 'TeamTitle'
FROM Tournament AS tr
JOIN TeamUser AS tu ON tu.id = tr.teamUserID
WHERE tr.seasonID > 62
-- 按州、teamUserID分组，并启用ROLLUP生成汇总行
GROUP BY tu.schoolState, tr.teamUserID WITH ROLLUP
-- 保持原排序逻辑
ORDER BY tu.schoolState, tr.teamUserID;

关键语法说明 #

• WITH ROLLUP：作为GROUP BY的扩展，它会自动生成分组汇总行和总计行，汇总的层次由GROUP BY的列顺序决定（从右往左逐步汇总）。
• GROUPING(column)：用来判断当前行是否是该列的汇总行：
  • 返回1：表示该行是该列的汇总行（该列的值为NULL）
  • 返回0：表示该行是普通的明细行
• CASE语句：用来格式化汇总行的显示内容，让小计、总计和明细行的区分更直观。

可选调整：小计显示具体数值 #

如果你希望州小计行直接显示汇总后的数值（比如CA的小计是25），可以把对应CASE分支改成：

WHEN GROUPING(tr.teamUserID) = 1 THEN CAST(SUM(COUNT(tr.teamMemberID)) AS CHAR)

修改后对应的州小计行就会显示具体的合计数，而不是文本标注。

期望查询结果示例 #

执行调整后的语句后，你会得到类似这样的结果：

+------------+-------------+----------------------------------------+
| Team Count | State       | TeamTitle                              |
+------------+-------------+----------------------------------------+
| 5          | CA          | Calvary Chapel Christian School Downey |
| 9          | CA          | Calvary Chapel High School             |
| 3          | CA          | Delta Charter High School              |
| 8          | CA          | Immanuel High School                   |
| 25         | CA          |                                        |
| 4          | CO          | Caliche High School                    |
| 10         | CO          | Del Norte High School                  |
| 14         | CO          | Faith Christian High School            |
| 28         | CO          |                                        |
| 8          | IL          | Altamont High School                   |
| 30         | IL          | Brimfield High School                  |
| 38         | IL          |                                        |
| 91         | All States  |                                        |
+------------+-------------+----------------------------------------+

内容的提问来源于stack exchange，提问作者Jim M