为何这两个MySQL查询结果不同？求解LeetCode即时配送问题

问题查询错误的核心原因 #

第一个CTE查询的问题出在GROUP BY customer_id的逻辑上：

• 按customer_id分组并选取MIN(order_date)作为首单日期时，直接选取customer_pref_delivery_date却未对其做聚合操作（比如关联匹配或用MIN/MAX约束）。
• 在MySQL非严格模式下，这种写法不会报错，但数据库会随机从该用户的所有订单中抽取一条的customer_pref_delivery_date值，这个值大概率不是对应首单（MIN(order_date)）的配送日期。
• 后续判断order_date = customer_pref_delivery_date时，用的是随机匹配的配送日期，自然导致即时配送比例计算错误。

正确查询的逻辑合理性 #

第二个查询的逻辑是准确的：

• 先通过子查询找出每个用户的首单日期，再用WHERE (customer_id, order_date) IN (...)筛选出所有首单的完整记录，此时每条记录的customer_pref_delivery_date都是该首单对应的真实配送日期。
• 基于这些真实的首单记录统计即时配送比例，结果自然正确。

修正问题查询的两种写法 #

写法一：用窗口函数获取首单记录

WITH first_orders AS (
    SELECT
        customer_id,
        order_date,
        customer_pref_delivery_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
    FROM Delivery
)
SELECT 
    ROUND(SUM(IF(order_date = customer_pref_delivery_date, 1, 0)) / COUNT(*) * 100, 2) AS immediate_percentage
FROM first_orders
WHERE rn = 1

写法二：用关联子查询匹配首单

WITH first_orders AS (
    SELECT
        d.customer_id,
        d.order_date,
        d.customer_pref_delivery_date
    FROM Delivery d
    JOIN (
        SELECT customer_id, MIN(order_date) AS min_order_date
        FROM Delivery
        GROUP BY customer_id
    ) t ON d.customer_id = t.customer_id AND d.order_date = t.min_order_date
)
SELECT 
    ROUND(SUM(IF(order_date = customer_pref_delivery_date, 1, 0)) / COUNT(*) * 100, 2) AS immediate_percentage
FROM first_orders

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