SQL查询需求：获取含成交与未成交状态的全部订单数据

需求：查询所有订单数据，包含产生交易的订单和未产生交易的订单。

已知数据表及数据 #

Buy_Orders表 #

SELECT * FROM Buy_Orders

Sell_Orders表 #

SELECT * FROM Sell_Orders

Transactions表 #

SELECT * FROM Transactions

交易逻辑说明 #

交易ID 113（卖单ID 23 + 买单ID 14）：订单23于2021-10-12 14:07:27.205进入订单簿后与订单14部分成交，因此在2021-10-12 14:07:27.206时，Sell_Orders与Buy_Orders表中订单23和14被更新：订单23以数量1重新进入订单簿，订单14以数量0重新进入订单簿。

错误的SQL查询 #

我尝试了以下SQL但未得到预期结果：

SELECT 
    o.OrderID
    o.OrderQuantity
    o.OrderPlacementDate
    t.TransactionID 
FROM (
    SELECT * 
from 
       Sell_Orders 
UNION 
SELECT *
from 
      Buy_Orders ) o
LEFT JOIN (
    SELECT 
      TransactioID -- 拼写错误：应为TransactionID
    FROM 
      Transactions ) t on t.SellOrderID = o.OrderID or t.BuyOrderID = o.OrderID

问题分析与修正后的SQL #

原SQL存在多个问题：

1. 字段间缺少逗号分隔，触发语法错误；
2. 子查询字段拼写错误：TransactioID应为TransactionID；
3. 使用UNION会自动去重，但同一个订单ID存在成交前后的不同版本，需要用UNION ALL保留所有订单记录；
4. 关联逻辑不精准：仅通过订单ID关联会导致成交前的订单版本错误匹配交易ID，需结合订单的数量变化（成交后数量减少）或时间维度判断。

修正后的SQL：

SELECT 
    o.OrderID,
    t.TransactionID,
    o.OrderQuantity,
    o.OrderPrice,
    o.OrderPlacementDate
FROM (
    SELECT * FROM Sell_Orders
    UNION ALL -- 保留所有订单版本，避免去重
    SELECT * FROM Buy_Orders
) o
LEFT JOIN Transactions t 
    -- 匹配成交后的卖单版本：数量小于该订单的最大数量，且时间对应成交更新时间
    ON (o.OrderID = t.SellOrderID AND o.OrderQuantity < (SELECT MAX(OrderQuantity) FROM Sell_Orders WHERE OrderID = t.SellOrderID))
    -- 匹配成交后的买单版本
    OR (o.OrderID = t.BuyOrderID AND o.OrderQuantity < (SELECT MAX(OrderQuantity) FROM Buy_Orders WHERE OrderID = t.BuyOrderID))
ORDER BY o.OrderPlacementDate, o.OrderID;

预期输出结果 #

OrderID   TransactionID   OrderQuantity   OrderPrice   OrderPlacementDate
---------------------------------------------------------------------------
12        NULL            11              103          2021-10-12 14:02:22.703  
14        NULL            6               100          2021-10-12 14:04:24.700
9         NULL            2               13           2021-10-12 14:05:25.705
23        NULL            7               100          2021-10-12 14:07:27.205 -----> 第一次交易
23        113             1               100          2021-10-12 14:07:27.206
14        113             0               100          2021-10-12 14:07:27.206
17        NULL            3               80           2021-10-12 14:08:22.703  
33        NULL            9               90           2021-10-12 14:08:28.403
90        NULL            1               103          2021-10-12 14:09:21.500 -----> 第二次交易
90        123             0               103          2021-10-12 14:09:21.501
12        123             0               103          2021-10-12 14:09:21.501
20        NULL            20              23           2021-10-12 14:11:23.705

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