多INNER JOIN关联查询下筛选Inv_Disp全为'I'的Order_Num对应记录

你完全不需要先把查询结果存入临时表再筛选，直接对原有查询做嵌套查询/公共表表达式（CTE）就能实现需求，以下是两种常用的实现方式：

方案1：窗口函数实现（推荐，代码更简洁） #

WITH base_data AS (
    SELECT OV.[inv_disp] AS Inv_Disp
          ,OV.[solineid] AS SO_Line_ID
          ,SO.[order_num] AS Order_Num
          ,SOL.[SOID] AS SO_ID
          ,SOL.[OrderDate] AS Order_Date
          -- 按订单号分组，统计该订单下非'I'的Inv_Disp数量
          ,COUNT(CASE WHEN OV.[inv_disp] != 'I' THEN 1 END) OVER (PARTITION BY SO.[order_num]) AS non_i_cnt
    FROM [Order_View] OV
    INNER JOIN [dbo].[SOline] SOL on OV.solineid = SOL.lineid
    INNER JOIN [dbo].[SO] SO on SOL.SOQTID = SO.SOQTID
)
SELECT Inv_Disp, SO_Line_ID, Order_Num, SO_ID, Order_Date
FROM base_data
WHERE non_i_cnt = 0

逻辑说明：按Order_Num分组统计每个订单里Inv_Disp不等于'I'的记录数，只要这个值为0就说明该订单所有行的Inv_Disp都是'I'，直接筛选即可。

方案2：子查询过滤符合条件的订单号（兼容低版本数据库） #

如果你的数据库版本不支持窗口函数，可以用这种更通用的写法：

SELECT OV.[inv_disp] AS Inv_Disp
      ,OV.[solineid] AS SO_Line_ID
      ,SO.[order_num] AS Order_Num
      ,SOL.[SOID] AS SO_ID
      ,SOL.[OrderDate] AS Order_Date
FROM [Order_View] OV
INNER JOIN [dbo].[SOline] SOL on OV.solineid = SOL.lineid
INNER JOIN [dbo].[SO] SO on SOL.SOQTID = SO.SOQTID
WHERE SO.[order_num] IN (
    -- 先查出所有Inv_Disp全为'I'的订单号
    SELECT SO_inner.[order_num]
    FROM [Order_View] OV_inner
    INNER JOIN [dbo].[SOline] SOL_inner on OV_inner.solineid = SOL_inner.lineid
    INNER JOIN [dbo].[SO] SO_inner on SOL_inner.SOQTID = SO_inner.SOQTID
    GROUP BY SO_inner.[order_num]
    HAVING MAX(CASE WHEN OV_inner.[inv_disp] != 'I' THEN 1 ELSE 0 END) = 0
)

以上两种方案都可以直接执行，不需要额外创建临时表，性能和临时表方案基本一致，还能减少额外的IO开销。

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