SQL查询错误分析：分组未按预期获取用户最早订单日期

问题背景 #

表结构与数据 #

Create table If Not Exists Delivery (delivery_id int, customer_id int, order_date date, customer_pref_delivery_date date);
Truncate table Delivery;
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('1', '1', '2019-08-01', '2019-08-02');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('2', '2', '2019-08-02', '2019-08-02');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('3', '1', '2019-08-11', '2019-08-12');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('4', '3', '2019-08-24', '2019-08-24');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('5', '3', '2019-08-21', '2019-08-22');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('6', '2', '2019-08-11', '2019-08-13');
insert into Delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) values ('7', '4', '2019-08-09', '2019-08-09');

需求与错误SQL #

需求：获取每个用户的最早订单记录，最终结果按customer_id升序排列。

用户编写的错误SQL：

with t1 as (select *
            from delivery
            order by customer_id, order_date),
t2 as (select * from t1 group by customer_id)
select * from t2;

执行后发现customer_id=3的记录，order_date为2019-08-24，而非预期的2019-08-21。

错误原因解析 #

1. CTE中的ORDER BY不保证后续数据集的顺序 #

CTE（公共表表达式）本质是一个临时数据集，如果没有搭配LIMIT子句，数据库优化器会忽略CTE内部的ORDER BY。这是因为SQL是基于集合的语言，集合本身是无序的，除非在最终的查询中明确指定ORDER BY，否则数据库不会保证数据的排列顺序。所以t1的排序对后续t2的查询没有任何约束作用。

2. GROUP BY的非标准用法导致结果随机 #

select * from t1 group by customer_id属于不符合SQL标准的写法（仅部分数据库如MySQL在关闭ONLY_FULL_GROUP_BY参数时允许）。当使用GROUP BY分组时，SELECT子句中未被聚合的字段，数据库会随机选取该分组内的任意一行数据，完全不会遵循之前的排序逻辑。这就是为什么customer_id=3的分组里，最终取到的是order_date=2019-08-24的记录，而非更早的那条。

正确实现方式 #

如果要获取每个用户的最早订单记录，推荐以下两种标准写法：

方法1：使用窗口函数ROW_NUMBER() #

通过窗口函数为每个用户的订单按order_date排序，标记出最早的那条：

WITH ranked_delivery AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS rn
    FROM Delivery
)
SELECT delivery_id, customer_id, order_date, customer_pref_delivery_date
FROM ranked_delivery
WHERE rn = 1
ORDER BY customer_id;

方法2：使用聚合函数关联子查询 #

先通过聚合函数找到每个用户的最早订单日期，再关联原表获取完整记录：

SELECT d.*
FROM Delivery d
INNER JOIN (
    SELECT customer_id, MIN(order_date) AS earliest_order_date
    FROM Delivery
    GROUP BY customer_id
) t ON d.customer_id = t.customer_id AND d.order_date = t.earliest_order_date
ORDER BY d.customer_id;

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