SQL按User_id分组取前2笔、最新购买记录及总量，解决GROUP BY报错问题

你写的SQL触发报错是SQL的GROUP BY语法规则导致的：使用GROUP BY分组后，SELECT子句中的字段要么是GROUP BY指定的分组键（这里仅User_id），要么必须被聚合函数包裹。Serviceid既不是分组键也没有加聚合逻辑，数据库无法确定同一个User_id下要返回哪一条的Serviceid，因此抛出错误。

不需要多次聚合再关联，用窗口函数+条件聚合一次查询就能实现所有需求，性能远高于多表JOIN的方案。

实现思路 #

1. 对每个用户的购买记录加两个排序序号：
   • 按购买日期升序排序的正序序号，取序号1、2对应最早的两个购买记录
   • 按购买日期降序排序的倒序序号，取序号1对应最新的购买记录
2. 用窗口函数预计算每个用户的累计购买总数
3. 用条件聚合把行数据转成你需要的列格式

完整SQL代码 #

以下是兼容MySQL 8.0+/PostgreSQL/SQL Server的标准SQL实现，注意日期转义函数需要根据你使用的数据库调整：

WITH ranked_data AS (
    SELECT
        User_id,
        Serviceid,
        `Date`,
        -- 正序排序：日期升序，同日期按服务ID升序，可根据实际规则调整
        ROW_NUMBER() OVER (
            PARTITION BY User_id 
            ORDER BY STR_TO_DATE(`Date`, '%d-%b-%y') ASC, Serviceid ASC
        ) AS rn_asc,
        -- 倒序排序：日期降序，同日期按服务ID降序，可根据实际规则调整
        ROW_NUMBER() OVER (
            PARTITION BY User_id 
            ORDER BY STR_TO_DATE(`Date`, '%d-%b-%y') DESC, Serviceid DESC
        ) AS rn_desc,
        -- 累计购买总次数，包含重复购买的相同服务
        COUNT(*) OVER (PARTITION BY User_id) AS TotalServices
    FROM purchases
)
SELECT
    User_id,
    MAX(CASE WHEN rn_asc = 1 THEN Serviceid END) AS FirstServiceid,
    MAX(CASE WHEN rn_asc = 2 THEN Serviceid END) AS SecondServiceid,
    MAX(CASE WHEN rn_asc = 1 THEN `Date` END) AS FirstDate,
    MAX(CASE WHEN rn_asc = 2 THEN `Date` END) AS SecondDate,
    MAX(CASE WHEN rn_desc = 1 THEN Serviceid END) AS LastServiceid,
    MAX(CASE WHEN rn_desc = 1 THEN `Date` END) AS LastDate,
    MAX(TotalServices) AS TotalServices
FROM ranked_data
GROUP BY User_id
ORDER BY User_id;

单独查询最新购买记录的解决方法 #

如果你仅需要查询每个用户最新的购买服务和日期，可以直接用FIRST_VALUE窗口函数实现，不会触发GROUP BY报错：

SELECT DISTINCT
    User_id,
    FIRST_VALUE(`Date`) OVER (
        PARTITION BY User_id 
        ORDER BY STR_TO_DATE(`Date`, '%d-%b-%y') DESC
    ) AS max_date,
    FIRST_VALUE(Serviceid) OVER (
        PARTITION BY User_id 
        ORDER BY STR_TO_DATE(`Date`, '%d-%b-%y') DESC
    ) AS last_serviceid
FROM purchases;

内容的提问来源于stack exchange，提问作者mannt-jp