SQL Server技术需求：仅返回无其他状态的ShipmentLookupCode的Cancelled记录

需求说明 #

仅返回taskStatuses为Cancelled的ShipmentLookupCode记录；若某ShipmentLookupCode同时存在Cancelled和Completed或Released状态，则不返回该记录。

原SQL查询语句 #

select      
            tv.ShipmentLookupCode,
            ts.name as taskStatuses

from        dbo.TasksView tv
join        dbo.TaskStatuses ts on tv.statusId = ts.id

where       tv.ShipmentLookupCode in('DS728352','DS727731','DS729480','DS730202','DS730222')
and         tv.operationCodeId=8

order by    tv.ShipmentLookupCode

当前查询结果 #

ShipmentLookupCode  taskStatuses
DS727731    Completed
DS727731    Completed
DS727731    Cancelled
DS727731    Completed
DS728352    Completed
DS728352    Cancelled
DS729480    Completed
DS729480    Cancelled
DS729480    Completed
DS729480    Cancelled
DS729480    Completed
DS729480    Cancelled
DS730202    Cancelled
DS730222    Cancelled
DS730222    Cancelled

期望返回结果 #

DS730202    Cancelled
DS730222    Cancelled
DS730222    Cancelled

修改后的SQL语句 #

方式一：子查询排除法 #

select
    tv.ShipmentLookupCode,
    ts.name as taskStatuses
from dbo.TasksView tv
join dbo.TaskStatuses ts on tv.statusId = ts.id
where 
    tv.ShipmentLookupCode in('DS728352','DS727731','DS729480','DS730202','DS730222')
    and tv.operationCodeId=8
    and ts.name = 'Cancelled'
    and tv.ShipmentLookupCode not in (
        select ShipmentLookupCode
        from dbo.TasksView tv_sub
        join dbo.TaskStatuses ts_sub on tv_sub.statusId = ts_sub.id
        where 
            tv_sub.ShipmentLookupCode in('DS728352','DS727731','DS729480','DS730202','DS730222')
            and tv_sub.operationCodeId=8
            and ts_sub.name in ('Completed', 'Released')
    )
order by tv.ShipmentLookupCode;

方式二：窗口函数统计法（性能更优） #

with status_summary as (
    select
        tv.ShipmentLookupCode,
        ts.name as taskStatuses,
        count(case when ts.name in ('Completed', 'Released') then 1 end) over (partition by tv.ShipmentLookupCode) as has_valid_status
    from dbo.TasksView tv
    join dbo.TaskStatuses ts on tv.statusId = ts.id
    where 
        tv.ShipmentLookupCode in('DS728352','DS727731','DS729480','DS730202','DS730222')
        and tv.operationCodeId=8
)
select
    ShipmentLookupCode,
    taskStatuses
from status_summary
where taskStatuses = 'Cancelled' and has_valid_status = 0
order by ShipmentLookupCode;

思路解析 #

1. 子查询排除法：先筛选出所有存在Completed或Released状态的ShipmentLookupCode，主查询中排除这些编码，同时只保留Cancelled状态的记录。
2. 窗口函数统计法：通过partition by按ShipmentLookupCode分组，统计每组中Completed或Released状态的数量，最终筛选出数量为0且状态为Cancelled的记录，无需重复编写过滤条件，执行效率更高。

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