GROUP BY分组查询未返回预期结果，特定用户日期起始异常排查

我目前碰到一个SQL查询的异常情况，想请大家帮忙分析原因：

我的查询语句如下：

SELECT last_name, first_name, DATE_FORMAT((tm_date), '%Y-%m-%d') AS dates, SUM(tm_hours) total 
FROM timecard 
LEFT JOIN teachers i ON i.ds_id = timecard.ds_id 
WHERE tm_notes != 'Lunch Break' 
  AND tm_date BETWEEN '2018-04-16' AND '2018-04-30' 
  AND timecard.ds_id = '4' 
GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), timecard.ds_id 
ORDER BY last_name ASC, dates ASC

异常现象 #

• 查询其他用户（不同timecard.ds_id）时，结果的第一条记录周起始日期都是2018-04-16，符合预期；
• 但针对ds_id='4'的用户，结果第一条记录的周起始是2018-04-20，而非预期的2018-04-16。

相关截图 #

查询结果截图 #

样本数据截图 #

排查思路和可能原因 #

我先来帮你梳理几个可能的方向，你可以一步步验证：

1. 确认该用户在4.16-4.19期间是否有符合条件的记录
   你的查询里加了tm_notes != 'Lunch Break'的过滤条件，会不会ds_id='4'的用户在4月16日到19日之间的所有记录，tm_notes都是'Lunch Break'？如果是这样，这些记录会被WHERE条件直接过滤掉，自然不会出现在结果里。
   你可以跑这条简单的查询验证一下：

   SELECT * FROM timecard 
   WHERE ds_id='4' 
     AND tm_date BETWEEN '2018-04-16' AND '2018-04-19' 
     AND tm_notes != 'Lunch Break';
   

   如果这条查询返回空，那基本就是这个原因了。

2. 检查MySQL周函数的模式设置
   MySQL的WEEK()函数的周起始规则由default_week_format参数控制，比如有的模式以周一为周起始，有的以周日为起始。不过这个可能不是核心原因（毕竟其他用户结果正常），但你可以确认一下当前设置：

   SELECT @@default_week_format;
   

   比如如果是模式0（周日起始），4月16日是周一、4月20日是周五，同属于第16周；但其他用户的分组里有16日的记录，所以显示16日，而这个用户的分组里最早的符合条件记录是20日，所以dates就显示20日了。

3. 修正GROUP BY后dates字段的取值逻辑
   你现在SELECT里的dates是直接取分组中某一条记录的tm_date格式化后的值（MySQL非严格模式下会随机取或取第一条）。其他用户的该周分组里有4月16日的记录，所以显示16日；而这个用户的该周分组里只有4月20日及之后的记录，就显示20日了。
   如果你想固定显示每周的起始日期（比如周一），可以修改dates的计算方式：

   DATE_FORMAT(DATE_SUB(tm_date, INTERVAL WEEKDAY(tm_date) DAY), '%Y-%m-%d') AS week_start_date
   

   这样不管分组里的记录是哪天，都会输出该周的周一日期，就不会出现这种异常了。

大概率是第一个原因导致的——这个用户在4.16-4.19期间没有符合过滤条件的记录，所以该周的结果里只能显示他有记录的最早日期20日。

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