SQL查询需求：筛选Current_Orders_tbl特定订单记录

最终查询语句 #

-- 匹配到对应transaction_id的order_id
SELECT DISTINCT order_id
FROM Current_Orders_tbl
WHERE transaction_id IS NOT NULL
AND transaction_id IN (SELECT order_id FROM Current_Orders_tbl)

UNION

-- 从未被用作transaction_id的order_id及空transaction_id
SELECT order_id, NULL AS transaction_id
FROM Current_Orders_tbl
WHERE order_id NOT IN (SELECT transaction_id FROM Current_Orders_tbl WHERE transaction_id IS NOT NULL);

逻辑说明 #

• 第一段子查询：筛选存在有效transaction_id，且该transaction_id对应表中某个order_id的订单，用DISTINCT避免重复返回相同order_id。
• 第二段子查询：筛选order_id从未出现在非空transaction_id列表中的订单，按需求返回order_id和空的transaction_id字段。
• 用UNION合并两个结果集，自动剔除重复记录。

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