MySQL销售表按代理拆分周内每日销售额的SQL查询方案

我在MySQL中有一张销售表NewDeals，希望通过单条SQL查询得到如下格式的结果：

AGENT, MONDAY_TOTAL, TUESDAY_TOTAL,WEDNESDAY_TOTAL,THURSDAY_TOTAL,FRIDAY_TOTAL,SATURDAY_TOTAL

最初的尝试 #

我最初编写的SQL语句为：

SELECT DISTINCT(repname), DAYOFWEEK(sub_date), COUNT(*) 
FROM `NewDeals` 
WHERE WEEK(sub_date) = WEEK(CURRENT_DATE) 
GROUP BY repname, DAYOFWEEK(sub_date)

该语句返回的结果格式为：

AGENT, DAYOFWEEK, TOTAL

虽然可以通过代码转换格式，但我更希望直接用SQL实现目标格式。

中间的尝试与问题 #

于是我编写了如下SQL，尝试用行转列的方式直接得到目标格式：

SELECT repname, 
       SUM(IF(DAYOFWEEK(sub_date)=2, 1, 0)) AS MONDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=3, 1, 0)) AS TUESDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=4, 1, 0)) AS WEDNESDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=5, 1, 0)) AS THURSDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=6, 1, 0)) AS FRIDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=7, 1, 0)) AS SATURDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=1, 1, 0)) AS SUNDAY_TOTAL
FROM `NewDeals` 
WHERE WEEK(sub_date) = WEEK(CURRENT_DATE) 
GROUP BY repname

但该语句会返回往年同周的销售数据，经排查发现问题出在未限制年份，导致跨年度的同周记录被统计进来。

最终解决方案 #

修正后，添加年份限制的SQL语句如下：

SELECT repname, 
       SUM(IF(DAYOFWEEK(sub_date)=2, 1, 0)) AS MONDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=3, 1, 0)) AS TUESDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=4, 1, 0)) AS WEDNESDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=5, 1, 0)) AS THURSDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=6, 1, 0)) AS FRIDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=7, 1, 0)) AS SATURDAY_TOTAL,
       SUM(IF(DAYOFWEEK(sub_date)=1, 1, 0)) AS SUNDAY_TOTAL
FROM `NewDeals` 
WHERE WEEK(sub_date) = WEEK(CURRENT_DATE) AND YEAR(sub_date) = YEAR(CURRENT_DATE)
GROUP BY repname

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