具有汇总和限制的MYSQL聚合
我需要获得每页前10行，这将返回由roomId分组的值之和。并有一个页脚来显示聚合值，超出限制。
SELECT roomId, SUM(value) FROM table A
GROUP BY roomId
WITH ROLLUP
LIMIT 0,10问题是，我需要汇总生成的摘要返回所有房间的和值，甚至那些外部限制(页面)，(聚合不应该使用限制)。
示例数据
+--------+------------+
| roomId | Sum(value) |
+========+============+
| 1 | 10 |
+--------+------------+
| 1 | 10 |
+--------+------------+
| 1 | 20 |
+--------+------------+
| 1 | 5 |
+--------+------------+
| 2 | 10 |
+--------+------------+
| 2 | 20 |
+--------+------------+
| 2 | 30 |
+--------+------------+
| 3 | 10 |
+--------+------------+
| 3 | 20 |
+--------+------------+
| NULL | 135 |
+--------+------------+但是，我希望得到汇总，使所有房间的值之和，而不仅仅是显示的第10行，聚合应该将所有数据库行加在一起，即使用户只是读取前10页。
因此，汇总应该如下所示：
+--------+------------+
| roomId | Sum(value) |
+========+============+
| ... | ... |
+--------+------------+
| NULL | 58935 |
+--------+------------+我知道我可以做两个不同的查询，或者使用UNION，但这是真的吗？不能用卷起来完成吗？使用2种不同的查询，汇总会更快。
回答 1
Stack Overflow用户
发布于 2022-05-24 18:15:48
困难之处在于，LIMIT是在GROUP BY ... WITH ROLLUP之后应用的，因此带有NULL的行只是要限制的行中的一行。
一种解决方案是对行进行排序，以便将汇总行放在第一位。然后您可以使用LIMIT并得到rollup行和N-1行。
mysql> SELECT roomId, SUM(value) FROM A GROUP BY roomId WITH ROLLUP
ORDER BY roomId LIMIT 3;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
| NULL | 135 |
| 1 | 45 |
| 2 | 60 |
+--------+------------+但是，这些行可能不是您想要显示的顺序。您必须获取结果并使用应用程序代码操作显示顺序。
此外，您还必须使用MySQL 8.0.12或更高版本才能在带有ROLLUP的查询中使用ORDER BY，根据文档：https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html中的说明
如果不使用MySQL 8.0.12，则可以使用子查询来完成：
mysql> SELECT * FROM (SELECT roomId, SUM(value) FROM A GROUP BY roomId WITH ROLLUP) result ORDER BY roomId LIMIT 3;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
| NULL | 300 |
| 1 | 25 |
| 2 | 25 |
+--------+------------+关于你的评论：
在聚合之后应用极限，包括在汇总计算之后。因此，汇总计算的总和包括所有结果，而不仅仅是包含在有限行集中的结果。这就是你说你想要的，对吧？
下面是一个演示：
insert into A values (1, 10), (1, 15), (2, 10), (2, 15), (3, 10), (3, 15), (4, 10), (4, 15), (5, 10), (5, 15), (6, 10), (6, 15), (7, 10), (7, 15), (8, 10), (8, 15), (9, 10), (9, 15), (10, 10), (10, 15), (11, 10), (11, 15), (12, 10), (12, 15);现在每个roomId应该有一个25的总和，对于12个房间，总数应该是12 * 25或300。
mysql> SELECT roomId, SUM(value) FROM A GROUP BY roomId WITH ROLLUP
-> ORDER BY roomId LIMIT 3;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
| NULL | 300 |
| 1 | 25 |
| 2 | 25 |
+--------+------------+如果汇总总数仅为限制范围内的汇总，则汇总总数应为50，而不是300。上面的示例显示，汇总包括所有这些，因为聚合是在限制之前完成的。
如果希望汇总只包括限制中的那些房间，则必须在子查询中执行限制，然后在外部查询中使用rollup重新进行聚合，因此它只适用于子查询返回的空间。
mysql> SELECT roomId, SUM(value) FROM (
-> SELECT roomId, SUM(value) AS value FROM A
-> GROUP BY roomId ORDER BY roomId LIMIT 3) AS t
-> GROUP BY roomId WITH ROLLUP;
+--------+------------+
| roomId | SUM(value) |
+--------+------------+
| 1 | 50 |
| 2 | 50 |
| 3 | 50 |
| NULL | 150 |
+--------+------------+https://stackoverflow.com/questions/72367136
复制相似问题
腾讯云开发者
