MySQL关联查询问题：按Session时间统计订单商品总数量

需求与表结构 #

数据库表结构 #

• Orders表：ID(int)
• Order_Items表：ID(int)、Order_ID(int)、Session_ID(int)、quantity(int)
• Sessions表：ID(int)、start_time(datetime)

核心需求 #

按sessions.start_time分组，统计所有order_items.quantity的总和。关键限制：部分order_items的Session_ID无效（为NULL或不存在于Sessions表），但同一Order_ID下至少存在一条含有效Session_ID的记录，需将该订单下所有商品数量归入对应Session的统计结果中。

示例数据与预期输出 #

Order_Items示例数据

假设Session_ID=1对应的start_time为2024-02-01，预期输出：

start_date  | SUM(qty)
------------|---------
2024-02-01  | 11

解决方案 #

方法1：子查询绑定订单与有效Session #

先通过子查询提取每个订单对应的有效Session_ID（即存在于Sessions表的Session_ID），再关联所有订单商品项和Sessions表进行统计：

SELECT
    DATE(s.start_time) AS start_date,
    SUM(oi.quantity) AS `SUM(qty)`
FROM
    Order_Items oi
JOIN (
    -- 提取每个订单的有效Session_ID（去重避免重复关联）
    SELECT DISTINCT
        oi_inner.Order_ID,
        oi_inner.Session_ID
    FROM
        Order_Items oi_inner
    JOIN Sessions s_inner ON oi_inner.Session_ID = s_inner.ID
) valid_order_session ON oi.Order_ID = valid_order_session.Order_ID
JOIN Sessions s ON valid_order_session.Session_ID = s.ID
GROUP BY
    DATE(s.start_time);

方法2：窗口函数标记有效Session #

利用窗口函数为每个订单下的所有商品项统一标记有效Session_ID，再关联统计：

SELECT
    DATE(s.start_time) AS start_date,
    SUM(oi.quantity) AS `SUM(qty)`
FROM (
    SELECT
        oi.*,
        -- 优先取当前订单下的有效Session_ID（存在于Sessions表）
        FIRST_VALUE(oi.Session_ID) OVER (
            PARTITION BY oi.Order_ID
            ORDER BY CASE 
                WHEN oi.Session_ID IS NOT NULL AND EXISTS (SELECT 1 FROM Sessions s WHERE s.ID = oi.Session_ID) 
                THEN 0 ELSE 1 END
        ) AS valid_session_id
    FROM
        Order_Items oi
) oi
JOIN Sessions s ON oi.valid_session_id = s.ID
GROUP BY
    DATE(s.start_time);

思路说明 #

两种方法的核心逻辑一致：先为每个订单绑定一个有效Session_ID，确保该订单下所有商品项无论自身Session_ID是否有效，都能归入正确的Session分组进行统计。

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