MySQL GROUP BY分组仅取首行缺失其余值，近6个月积分统计问题

问题根因 #

现有SQL的错误逻辑：

1. 内层按用户+月份分组后，每行仅对应单个月份的积分值，其余月份列均为NULL
2. 外层按user_id分组时未对m1~m6列做聚合处理，MySQL默认取分组后的第一条数据，导致其余月份的数值丢失
3. SUM聚合函数写在CASE WHEN条件内部，不符合行转列的逻辑要求

修正后的查询语句 #

SET @date='2021-07-31 23:59:59';
SELECT 
    pd.user_id,
    MAX(oc.date_added) as date_added,
    SUM(CASE WHEN DATE_FORMAT(pd.`date`,'%M-%Y') = DATE_FORMAT(@date - INTERVAL 1 MONTH,'%M-%Y') THEN purchase_point ELSE 0 END) AS m1,
    SUM(CASE WHEN DATE_FORMAT(pd.`date`,'%M-%Y') = DATE_FORMAT(@date - INTERVAL 2 MONTH,'%M-%Y') THEN purchase_point ELSE 0 END) AS m2,
    SUM(CASE WHEN DATE_FORMAT(pd.`date`,'%M-%Y') = DATE_FORMAT(@date - INTERVAL 3 MONTH,'%M-%Y') THEN purchase_point ELSE 0 END) AS m3,
    SUM(CASE WHEN DATE_FORMAT(pd.`date`,'%M-%Y') = DATE_FORMAT(@date - INTERVAL 4 MONTH,'%M-%Y') THEN purchase_point ELSE 0 END) AS m4,
    SUM(CASE WHEN DATE_FORMAT(pd.`date`,'%M-%Y') = DATE_FORMAT(@date - INTERVAL 5 MONTH,'%M-%Y') THEN purchase_point ELSE 0 END) AS m5,
    SUM(CASE WHEN DATE_FORMAT(pd.`date`,'%M-%Y') = DATE_FORMAT(@date - INTERVAL 6 MONTH,'%M-%Y') THEN purchase_point ELSE 0 END) AS m6
FROM 64_point_details AS pd
LEFT JOIN 64_users AS us ON us.user_id = pd.user_id
LEFT JOIN oc_customer AS oc ON oc.customer_id = us.store_id
WHERE 
    oc.date_added <= DATE_FORMAT(@date - INTERVAL 6 MONTH,'%Y-%m-%d 23:59:59')
    AND pd.user_id = 1358
    AND pd.date BETWEEN DATE_FORMAT(@date - INTERVAL 6 MONTH,'%Y-%m-%d 23:59:59') AND @date
GROUP BY pd.user_id
HAVING (m1 + m2 + m3 + m4 + m5 + m6) > 0;

优化说明 #

• 采用标准行转列写法：将CASE WHEN判断放到SUM()聚合函数内部，直接按用户单次分组即可得到所有月份的积分汇总
• 给非分组字段oc.date_added增加MAX()聚合函数，兼容MySQL开启ONLY_FULL_GROUP_BY sql_mode的场景
• 无对应消费的月份会返回0而非NULL，更符合业务统计习惯
• 去掉了不必要的子查询和二次分组，执行效率更高

内容的提问来源于stack exchange，提问作者Muhammed Raheez PC