SQL 报错：聚合函数无法与其他非分组字段混用

1. 问题

代码：

SELECT t.user_id, 
       MIN(t.date) first_buy_date, 
       MAX(t.date) second_buy_date, 
       t.cnt
FROM (
      SELECT *,
             ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) rank_1,
             COUNT(product_name) OVER(PARTITION BY user_id ORDER BY date) cnt
      FROM   order_info
      WHERE  date > '2025-10-15' 
             AND status = 'completed'
             AND product_name IN ('C++', 'Java', 'Python')
     ) AS t 
WHERE t.cnt >= 2 AND t.rank_1 <= 2
GROUP 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.cnt' 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)：每个组的累计工资