SQL分组求和如何将同表关联子频道数据计入父频道总和
你需要用递归CTE(公共表表达式) 来展开所有频道的父子层级关系,支持任意多级子频道的汇总需求。普通JOIN只能处理单层关联,无法覆盖多级嵌套的场景,且你之前的写法会过滤无父级的根频道,自然得不到正确结果。
完整可运行的SQL如下:
WITH RECURSIVE channel_hierarchy AS ( -- 锚点:每个频道自身作为初始根节点 SELECT guild_id, channel_id AS root_channel_id, channel_id AS sub_channel_id FROM statschannel UNION ALL -- 递归:向上遍历所有父级频道,把子频道关联到所有上层父频道 SELECT sc.guild_id, sc.parent_channel_id AS root_channel_id, ch.sub_channel_id FROM channel_hierarchy ch INNER JOIN statschannel sc ON ch.root_channel_id = sc.channel_id WHERE sc.parent_channel_id IS NOT NULL ) SELECT ch.guild_id AS "Guild ID", ch.root_channel_id AS "Channel ID", SUM(s.Messages) AS "Messages" FROM channel_hierarchy ch INNER JOIN statistic s ON ch.sub_channel_id = s.parent_channel_id GROUP BY ch.guild_id, ch.root_channel_id ORDER BY ch.guild_id, ch.root_channel_id;
实现逻辑说明
- 递归CTE首先会为每个频道生成一条「自身到自身」的映射记录
- 递归阶段会不断向上查找父频道,为每个子频道生成和所有上层父频道的映射,比如示例中的频道4会生成
(4,4)、(3,4)两条记录,代表频道4的消息需要同时计入自身和父频道3的统计 - 最后关联消息统计表,按根频道(也就是你要统计的目标频道)分组求和,就能得到包含所有子频道消息的汇总结果
该写法支持PostgreSQL、MySQL 8.0+、SQL Server等所有支持递归CTE的主流数据库,如果是不支持CTE的旧版本数据库,可以考虑用存储过程遍历层级实现。
内容的提问来源于stack exchange,提问作者Epic Programmer
相关产品推荐
相关产品推荐

