mysql - 如何在排序限制后获得 WITH ROLLUP

Question

我有以下查询来获取足球队的获胜次数：

SELECT
    IF(grouping(team) IS TRUE, 'All', team) AS team,
    sum(wins)                               AS num_wins
FROM test.seasons
GROUP BY team WITH ROLLUP
ORDER BY num_wins;

结果是这样的：

team  num_wins
All     2552
NE      125
PIT     102
GB      102
SEA     100
...

有没有办法WITH ROLLUP在限制后做一个？例如，我如何才能在LIMIT 2（忽略汇总的限制）上获得以下值？

team   num_wins
All     227
NE      125
PIT     102

如果我做一个“正常限制”，它包括WITH ROLLUP在订单/限制之前评估的：

SELECT
    IF(grouping(team) IS TRUE, 'All', team) AS team,
    sum(wins)                               AS num_wins
FROM test.seasons
GROUP BY team WITH ROLLUP
ORDER BY num_wins DESC limit 2;

# team, num_wins
All 2552
NE  125

标签： mysqlsql

Answer 1

您将使用子查询：

SELECT (CASE WHEN grouping(team) THEN 'All' ELSE team END) AS team,
       SUM(wins)  AS num_wins
FROM (SELECT s.*
      FROM test.seasons s
      ORDER BY num_wins DESC
      LIMIT 2
     ) s
GROUP BY team WITH ROLLUP
ORDER BY num_wins;