文章目录

1. 问题

代码：

SELECT t.user_id, MIN(t.date) first_buy_date, MAX(t.date) second_buy_date, tFROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) rank_1,COUNT(product_name) OVER(PARTITION BY user_id ORDER BY date) cntFROM order_infoWHERE date > '2025-10-15' AND status = 'completed'AND product_name IN ('C++', 'Java', 'Python')) AS t WHERE t >= 2 AND t.rank_1 <= 2GROUP BY t.user_id ORDER BY t.user_id;

报错：

SQL_ERROR_INFO: "Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

2.解决

问题的原因是聚合函数无法与其它非分组字段在一起使用

所以需要讲子查询SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) rank_1,中的偷懒写法SELECT *改为相应的字段进行提取：SELECT user_id, date

注：其实的代码中还有一处 bug（不符合题意的地方）：

不应该是

COUNT(product_name) OVER(PARTITION BY user_id ORDER BY date)

而应该是

COUNT(product_name) OVER(PARTITION BY user_id )

这里涉及到 窗口函数OVER()的用法，简单来说，有四种用法（拿count工资举例）：

count() over() ：总工资count() over( partition by group )：每个组的总工资count() over(order by date)：累计工资count() over(partition by group order by date)：每个组的累计工资