MySQL子查询返回全部记录同时获取列总和问题求助

嗨，我来帮你搞定这个需求！你遇到的问题其实是对SQL聚合逻辑的理解偏差，我给你拆解一下：

为什么之前的方法不行？ #

• 直接在SELECT里加SUM(track_count) AS total时，外层查询没有指定GROUP BY，数据库会把所有行聚合为一条，所以只返回了一条记录；
• 使用WITH ROLLUP是在子查询的分组结果末尾添加一行汇总记录，而不是给每条原有记录都带上总和，所以不符合你的需求。

最优解决方案：窗口函数（推荐） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server等），这是最简洁的方法。窗口函数可以在不改变原有分组结果的前提下，计算全局或分区的聚合值：

SELECT 
    track_date AS label, 
    track_count AS value,
    SUM(track_count) OVER() AS total  -- 计算整个结果集的track_count总和
FROM trackers 
INNER JOIN ( 
    SELECT 
        tracker_details.track_id, 
        COUNT(track_id) AS track_count, 
        DATE(date_created) AS track_date 
    FROM tracker_details 
    WHERE (DATE(date_created) >= '2018-01-21' AND DATE(date_created) <= '2018-01-27') 
        AND client_id = 1 
    GROUP BY DATE(date_created)
) as tracker_details ON trackers.track_id = tracker_details.track_id 
WHERE trackers.client_id = 1 
ORDER BY DATE(track_date)

SUM(track_count) OVER()里的OVER()没有指定分区条件，所以会计算整个外层结果集的总和，这样每条记录都会带上你想要的总合计16。

兼容旧数据库的方案（比如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以先单独计算总合计，再通过交叉连接（CROSS JOIN）把总和关联到每条记录上：

方法1：使用CTE（公共表表达式） #

WITH total_summary AS (
    -- 先算出指定时间范围内的总记录数
    SELECT COUNT(track_id) AS total
    FROM tracker_details 
    WHERE (DATE(date_created) >= '2018-01-21' AND DATE(date_created) <= '2018-01-27') 
        AND client_id = 1
)
SELECT 
    track_date AS label, 
    track_count AS value,
    total
FROM trackers 
INNER JOIN ( 
    SELECT 
        tracker_details.track_id, 
        COUNT(track_id) AS track_count, 
        DATE(date_created) AS track_date 
    FROM tracker_details 
    WHERE (DATE(date_created) >= '2018-01-21' AND DATE(date_created) <= '2018-01-27') 
        AND client_id = 1 
    GROUP BY DATE(date_created)
) as tracker_details ON trackers.track_id = tracker_details.track_id 
CROSS JOIN total_summary  -- 把总和和每条记录关联
WHERE trackers.client_id = 1 
ORDER BY DATE(track_date)

方法2：使用子查询 #

如果连CTE都不支持，直接把总合计写成子查询：

SELECT 
    track_date AS label, 
    track_count AS value,
    -- 子查询计算总合计
    (SELECT COUNT(track_id) 
     FROM tracker_details 
     WHERE (DATE(date_created) >= '2018-01-21' AND DATE(date_created) <= '2018-01-27') 
         AND client_id = 1) AS total
FROM trackers 
INNER JOIN ( 
    SELECT 
        tracker_details.track_id, 
        COUNT(track_id) AS track_count, 
        DATE(date_created) AS track_date 
    FROM tracker_details 
    WHERE (DATE(date_created) >= '2018-01-21' AND DATE(date_created) <= '2018-01-27') 
        AND client_id = 1 
    GROUP BY DATE(date_created)
) as tracker_details ON trackers.track_id = tracker_details.track_id 
WHERE trackers.client_id = 1 
ORDER BY DATE(track_date)

这几种方法都能得到你期望的结果：每条每日统计记录后面都显示总合计16。

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