SQL查询需求：筛选同时含ORGNSCN和TRANSITRCV且顺序特定的记录

咱们先拆解下你的需求：要找出同时包含ORGNSCN和TRANSITRCV两种状态，并且**TRANSITRCV状态发生在ORGNSCN之前**的发票编号（比如示例中的FCI150142）。先说说你原查询的核心问题：

原查询的逻辑漏洞 #

你写的WHERE [StageID] = 'ORGNSCN' AND [StageID] = 'TRANSITRCV'是完全矛盾的——同一行记录的StageID不可能同时等于两个不同的值，所以这个条件永远不会返回任何数据。另外，GROUP BY同时包含InvoiceNumber和StageID，会把每个状态单独分组，根本没法判断同一个发票下两个状态的先后关系。

正确的解决方案 #

首先假设你的BoxesUpdateLog表有一个记录状态发生时间的字段（比如LogTime，这是判断先后的关键；如果没有的话，可依赖自增ID这类有序字段）。下面提供两种常用的实现方式：

方式一：自连接查询（直观易懂）

SELECT DISTINCT o.[InvoiceNumber]
FROM [CARGODB].[dbo].[BoxesUpdateLog] o
INNER JOIN [CARGODB].[dbo].[BoxesUpdateLog] t
    ON o.[InvoiceNumber] = t.[InvoiceNumber]
WHERE o.[StageID] = 'ORGNSCN'
    AND t.[StageID] = 'TRANSITRCV'
    AND t.LogTime < o.LogTime;

逻辑说明：把表和自身做连接，找到同一个发票下，TRANSITRCV状态的记录时间早于ORGNSCN状态的记录，最后用DISTINCT去重得到唯一的发票编号。

方式二：窗口函数（高效适配大数据量）

WITH InvoiceStatuses AS (
    SELECT 
        [InvoiceNumber],
        [StageID],
        LogTime,
        -- 计算每个发票下TRANSITRCV的最早发生时间
        MIN(CASE WHEN StageID = 'TRANSITRCV' THEN LogTime END) OVER (PARTITION BY InvoiceNumber) AS TransitFirstTime,
        -- 计算每个发票下ORGNSCN的最晚发生时间
        MAX(CASE WHEN StageID = 'ORGNSCN' THEN LogTime END) OVER (PARTITION BY InvoiceNumber) AS OrgLastTime
    FROM [CARGODB].[dbo].[BoxesUpdateLog]
    WHERE StageID IN ('ORGNSCN', 'TRANSITRCV') -- 先过滤无关状态，提升查询效率
)
SELECT DISTINCT [InvoiceNumber]
FROM InvoiceStatuses
WHERE TransitFirstTime IS NOT NULL -- 确保存在TRANSITRCV状态
    AND OrgLastTime IS NOT NULL -- 确保存在ORGNSCN状态
    AND TransitFirstTime < OrgLastTime; -- 确保TRANSITRCV更早发生

逻辑说明：先用窗口函数按发票分组，计算每个发票的两个目标状态的时间极值，再筛选出同时满足两个状态存在且时间顺序符合要求的发票。

补充适配 #

如果你的表没有时间字段，只有类似自增的LogID（假设ID越大记录越晚），只需要把上面的LogTime换成LogID即可，判断条件改成t.LogID < o.LogID或者TransitFirstID < OrgLastID。

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