MySQL三表关联查询：将相同日期的库存更新与订单记录合并为一行

问题原因 #

你当前的SQL是先左关联stock_updates表，以库存记录为基准匹配订单，自然会漏掉没有对应库存记录的订单（也就是你缺的2021-06-01那条记录）。

解决方法 #

我们可以先把两个业务表中所有的商品+日期组合取出来，再分别匹配当天的库存记录和订单记录，就能覆盖「仅库存更新」「仅下单」「两者都有」三种场景：

MySQL 8.0及以上（支持CTE语法）

WITH all_product_dates AS (
    -- 取出两个表中所有存在记录的商品+日期组合
    SELECT product_id, DATE(registered_at) AS record_date FROM stock_updates
    UNION
    SELECT product_id, DATE(ordered_at) AS record_date FROM order_items
),
daily_stock AS (
    -- 按商品+日期聚合库存记录
    SELECT 
        product_id,
        DATE(registered_at) AS record_date,
        MAX(registered_at) AS registered_at
    FROM stock_updates
    GROUP BY product_id, DATE(registered_at)
),
daily_orders AS (
    -- 按商品+日期聚合订单记录
    SELECT 
        product_id,
        DATE(ordered_at) AS record_date,
        MAX(ordered_at) AS ordered_at
    FROM order_items
    GROUP BY product_id, DATE(ordered_at)
)
SELECT 
    apd.product_id,
    ds.registered_at,
    dos.ordered_at
FROM all_product_dates apd
-- 匹配当天库存记录
LEFT JOIN daily_stock ds 
    ON apd.product_id = ds.product_id 
    AND apd.record_date = ds.record_date
-- 匹配当天订单记录
LEFT JOIN daily_orders dos 
    ON apd.product_id = dos.product_id 
    AND apd.record_date = dos.record_date
-- 如果需要过滤掉products表中不存在的商品，加上下面这行即可
-- INNER JOIN products p ON apd.product_id = p.id
ORDER BY apd.record_date;

MySQL 5.x版本（不支持CTE）

SELECT 
    apd.product_id,
    ds.registered_at,
    dos.ordered_at
FROM (
    SELECT product_id, DATE(registered_at) AS record_date FROM stock_updates
    UNION
    SELECT product_id, DATE(ordered_at) AS record_date FROM order_items
) apd
LEFT JOIN (
    SELECT 
        product_id,
        DATE(registered_at) AS record_date,
        MAX(registered_at) AS registered_at
    FROM stock_updates
    GROUP BY product_id, DATE(registered_at)
) ds ON apd.product_id = ds.product_id AND apd.record_date = ds.record_date
LEFT JOIN (
    SELECT 
        product_id,
        DATE(ordered_at) AS record_date,
        MAX(ordered_at) AS ordered_at
    FROM order_items
    GROUP BY product_id, DATE(ordered_at)
) dos ON apd.product_id = dos.product_id AND apd.record_date = dos.record_date
ORDER BY apd.record_date;

注：如果同一天存在多条库存更新/下单记录，你可以根据业务需求修改聚合逻辑，比如用GROUP_CONCAT拼接所有时间，或者取最新/最早的时间即可。

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