T-SQL高效实现：查询当前订单各订单项的最近历史同商品订单项

你可以利用窗口函数（比如ROW_NUMBER()）结合自连接实现单条语句批量查询，比循环或多次子查询更高效，适合处理给定订单的所有订单项。

实现思路 #

1. 关联order和orderitem表，获取订单与订单项的完整关联信息
2. 按「客户ID+商品ID」分组，对组内历史订单按时间倒序排序，为每条记录标记顺序编号
3. 筛选出给定订单中每个订单项对应的编号为1的历史记录（即最近的那条）

具体SQL语句 #

假设目标订单ID为:targetOrderID，可执行以下语句：

WITH ordered_items AS (
    SELECT 
        oi_current.orderitemid AS current_item_id,
        oi_current.articleid,
        o_current.customerid,
        o_current.datetime AS current_datetime,
        oi_history.orderitemid AS history_item_id,
        o_history.datetime AS history_datetime,
        -- 按客户+商品分组，历史订单时间越近编号越小
        ROW_NUMBER() OVER (
            PARTITION BY o_current.customerid, oi_current.articleid 
            ORDER BY o_history.datetime DESC
        ) AS rn
    FROM orderitem oi_current
    JOIN [order] o_current ON oi_current.orderid = o_current.orderid
    -- 自连接匹配同一客户、同一商品的更早订单
    LEFT JOIN [order] o_history 
        ON o_history.customerid = o_current.customerid
        AND o_history.datetime < o_current.datetime
    LEFT JOIN orderitem oi_history 
        ON o_history.orderid = oi_history.orderid
        AND oi_history.articleid = oi_current.articleid
    -- 指定要查询的目标订单
    WHERE o_current.orderid = :targetOrderID
)
SELECT 
    current_item_id,
    articleid,
    customerid,
    current_datetime,
    history_item_id,
    history_datetime
FROM ordered_items
WHERE rn = 1;

优化说明 #

• 用LEFT JOIN兼容无历史记录的订单项（此时history_item_id会返回NULL）
• 建议在order.customerid、order.datetime、orderitem.articleid字段建立联合索引，能大幅提升查询速度

替代方案（支持LATERAL JOIN的数据库） #

如果你的数据库（如PostgreSQL、SQL Server 2016+）支持横向连接，可使用更直观的写法：

SELECT 
    oi_current.orderitemid AS current_item_id,
    oi_current.articleid,
    o_current.customerid,
    o_current.datetime AS current_datetime,
    history_items.*
FROM orderitem oi_current
JOIN [order] o_current ON oi_current.orderid = o_current.orderid
LEFT JOIN LATERAL (
    SELECT TOP 1 
        oi_history.orderitemid,
        o_history.datetime
    FROM orderitem oi_history
    JOIN [order] o_history ON oi_history.orderid = o_history.orderid
    WHERE o_history.customerid = o_current.customerid
        AND o_history.datetime < o_current.datetime
        AND oi_history.articleid = oi_current.articleid
    ORDER BY o_history.datetime DESC
) AS history_items ON 1=1
WHERE o_current.orderid = :targetOrderID;

这种写法直接为每个当前订单项关联其最近的历史记录，逻辑更易懂，性能与窗口函数方案相近。

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