SQL按当前周每日最后值求和错误排查

测试数据：

5 for 2023-01-12 16:53:01
2 for 2023-01-12 08:49:03
5 for 2023-01-11 08:58:19

预期结果为10，但执行以下SQL后得到5，问题出在哪？

原SQL代码：

SELECT SUM(value) AS weeklyValue
        FROM (
            SELECT value
            FROM table
            WHERE WEEK(DATE(DataOra)) = WEEK(NOW())
            AND WEEKDAY(DATE(DataOra)) >= 1
            AND WEEKDAY(DATE(DataOra)) <= 7
            AND DataOra = (SELECT MAX(DataOra) FROM table WHERE WEEKDAY(DataOra) = WEEKDAY(DATE(DataOra)) )
            GROUP BY WEEKDAY(DATE(DataOra))
        ) AS subquery;

问题分析 #

1. WEEKDAY范围逻辑错误：WEEKDAY()函数返回值是0（周一）到6（周日），你写的>=1 AND <=7不仅会过滤掉周一的记录，且7是无效取值，逻辑本身不严谨。
2. 子查询未关联外层数据且无周限定：这是核心问题，DataOra = (SELECT MAX(DataOra) FROM table WHERE WEEKDAY(DataOra) = WEEKDAY(DATE(DataOra)) )这个子查询：
   • 没有关联外层表的行，相当于在全表中找同星期几的最大时间，而非当前周内该日期的最大时间。
   • 结合GROUP BY WEEKDAY，最终只会选出每个星期几中时间等于全局最大的那条记录，而非当前周内每天的最后一条。比如测试数据中2023-01-11的记录会因无法匹配对应日期的最大时间被过滤，最终只统计了1月12日的5，得到结果5。

修正后的SQL #

正确思路是先找出当前周内每天的最大时间，再关联原表获取对应value求和（加上YEAR()判断避免跨年周的问题）：

SELECT SUM(t.value) AS weeklyValue
FROM `table` t
INNER JOIN (
    SELECT DATE(DataOra) AS day_date, MAX(DataOra) AS max_time
    FROM `table`
    WHERE WEEK(DATE(DataOra)) = WEEK(NOW())
      AND YEAR(DataOra) = YEAR(NOW())
    GROUP BY day_date
) AS daily_max ON t.DataOra = daily_max.max_time;

这个查询会先按日期分组，找出当前周内每天的最后一条记录的时间，再通过关联拿到对应的value，最后求和得到预期的10。

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