SQL Server中如何筛选满足特定全行列条件的订单编号

原查询语句的问题 #

你之前的查询存在两个核心问题：

1. 直接关联Orders和OrderItems会返回与子订单条目数一致的重复行；
2. ALL子句的逻辑错误，无法有效过滤存在Received='B'的订单，也没准确满足第二个查询条件。

高效实现方案（两种可选） #

方案一：使用EXISTS和NOT EXISTS（推荐，性能最优）

利用SQL Server对EXISTS/NOT EXISTS的优化特性，直接针对订单级条件判断，无需聚合或返回重复行：

SELECT o.OrderNumber
FROM Orders o
WHERE 
    -- 条件1：该订单所有OrderItems的Received都不为'B'
    NOT EXISTS (
        SELECT 1 
        FROM OrderItems oi 
        WHERE oi.OrderID = o.OrderID 
          AND oi.Received = 'B'
    )
    -- 条件2：该订单至少有一行满足Received='X'且Scheduled='B'
    AND EXISTS (
        SELECT 1 
        FROM OrderItems oi 
        WHERE oi.OrderID = o.OrderID 
          AND oi.Received = 'X' 
          AND oi.Scheduled = 'B'
    )
    -- 如需指定特定订单，保留此条件；否则移除
    AND o.OrderNumber = '1234';

方案二：使用GROUP BY + HAVING（适合需同时聚合其他字段的场景）

通过分组聚合判断订单是否满足条件，自动去重：

SELECT o.OrderNumber
FROM Orders o
JOIN OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY o.OrderID, o.OrderNumber
HAVING 
    -- 条件1：所有Received都不为'B'（即无Received='B'的行）
    MAX(CASE WHEN oi.Received = 'B' THEN 1 ELSE 0 END) = 0
    -- 条件2：至少有一行满足Received='X'且Scheduled='B'
    AND MAX(CASE WHEN oi.Received = 'X' AND oi.Scheduled = 'B' THEN 1 ELSE 0 END) = 1
    -- 如需指定特定订单，保留此条件；否则移除
    AND o.OrderNumber = '1234';

性能说明 #

两种方案均无需临时表，只要在OrderItems表上创建包含OrderID、Received、Scheduled的复合索引（示例：CREATE NONCLUSTERED INDEX IX_OrderItems_OrderID_Received_Scheduled ON OrderItems(OrderID, Received, Scheduled);），大数据量下也能高效执行，SQL Server会快速定位符合条件的订单。

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