如何在SQL查询中新增无需额外分组的订单数辅助列

实现方案 #

1. 生成带订单数辅助列的全量结果 #

你这个需求不需要分组合并行，直接用窗口函数即可实现，语法简洁执行效率高：

SELECT 
    Email,
    ID,
    Order_date,
    Total,
    COUNT(*) OVER (PARTITION BY Email) AS `Number of orders`
FROM 你的实际表名;

逻辑说明：PARTITION BY Email会按邮箱维度统计每个邮箱的总订单数，直接把统计结果映射到对应邮箱的所有行上，完全保留原始表的所有行数据，和你期望的输出结果完全一致。

2. 基于辅助列做后续统计 #

你需要筛选订单数区间、计算金额均值/总和时，直接把上面的查询作为子查询嵌套，在外层加筛选条件即可：

示例：计算订单数大于1且小于5的所有订单的平均金额

SELECT AVG(Total) AS avg_order_total
FROM (
    SELECT 
        Email,
        ID,
        Order_date,
        Total,
        COUNT(*) OVER (PARTITION BY Email) AS order_cnt
    FROM 你的实际表名
) AS temp_table
WHERE order_cnt > 1 AND order_cnt < 5;

注意你之前的写法误区：不需要用GROUP BY + HAVING，分组会导致行合并，直接在子查询外层加WHERE筛选窗口函数的统计结果即可。

兼容旧版数据库的替代方案 #

如果你使用的数据库不支持窗口函数（比如MySQL 5.7及更低版本），可以用子查询关联的方式实现，效果完全一致：

SELECT 
    t1.Email,
    t1.ID,
    t1.Order_date,
    t1.Total,
    t2.order_cnt AS `Number of orders`
FROM 你的实际表名 t1
LEFT JOIN (
    SELECT Email, COUNT(*) AS order_cnt
    FROM 你的实际表名
    GROUP BY Email
) t2 ON t1.Email = t2.Email;

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