如何不使用Union查询所有活跃记录及首个非活跃记录（SQL）

需求是从产品表中选取所有活跃（IsActive=1）记录，以及每个产品的第一条非活跃（IsActive=0）记录。相比你当前用UNION的两次表扫描方案，以下两种方式更高效：

方案一：窗口函数单表扫描实现 #

利用窗口函数ROW_NUMBER()对每个产品的非活跃记录按月份排序，标记出第一条，再结合条件筛选出目标数据，全程只需扫描一次表：

-- 示例表结构与数据插入（保留原代码）
CREATE table #ActiveProduct
(
PRODUCT_ID INT NOT NULL,
Product_Month INT NOT NULL,
IsActive bit not null
)

INSERT into  #ActiveProduct
values (1,202001,1)
,(1,202001,1)
,(1,202002,1)
,(1,202003,1)
,(1,202004,1)
,(1,202005,0)
,(1,202006,0)
,(1,202007,0)
,(2,202001,1)
,(2,202002,1)
,(2,202003,1)
,(2,202004,1)
,(2,202005,1)
,(3,202002,1)
,(3,202003,0)
,(3,202005,0)

-- 核心查询语句
WITH ProductStatus AS (
    SELECT 
        PRODUCT_ID,
        Product_Month,
        IsActive,
        ROW_NUMBER() OVER (PARTITION BY PRODUCT_ID, IsActive ORDER BY Product_Month) AS rn
    FROM #ActiveProduct
)
SELECT PRODUCT_ID, Product_Month, IsActive
FROM ProductStatus
WHERE 
    IsActive = 1 
    OR (IsActive = 0 AND rn = 1);

方案优势 #

• 仅需一次表扫描，避免UNION带来的两次扫描开销，数据量越大效率提升越明显
• 逻辑简洁，直接通过窗口函数标记目标记录，无需额外关联操作

方案二：聚合关联+UNION ALL实现 #

先通过聚合函数找到每个产品最早的非活跃月份，再关联原表取出对应记录，同时用UNION ALL替代UNION（因为两个数据集无重复，无需去重），进一步提升性能：

-- 先获取每个产品最早的非活跃月份
WITH FirstInactive AS (
    SELECT 
        PRODUCT_ID,
        MIN(Product_Month) AS FirstInactiveMonth
    FROM #ActiveProduct
    WHERE IsActive = 0
    GROUP BY PRODUCT_ID
)
-- 合并活跃记录和第一条非活跃记录
SELECT ap.PRODUCT_ID, ap.Product_Month, ap.IsActive
FROM #ActiveProduct ap
WHERE ap.IsActive = 1
UNION ALL
SELECT ap.PRODUCT_ID, ap.Product_Month, ap.IsActive
FROM #ActiveProduct ap
JOIN FirstInactive fi 
    ON ap.PRODUCT_ID = fi.PRODUCT_ID 
    AND ap.Product_Month = fi.FirstInactiveMonth
    AND ap.IsActive = 0;

方案优势 #

• 利用MIN()聚合快速定位每个产品的第一条非活跃记录，关联逻辑清晰
• UNION ALL比UNION少了去重步骤，性能更优

两种方案都能输出符合预期的结果：所有活跃记录+每个产品最早的那条非活跃记录，产品2因无非活跃记录，只会输出其全部活跃数据。

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