如何让SQL查询在特定分支无替代UOM时忽略UOM过滤条件？

原SQL查询 #

select [itemcode], [uom], [price], [branch]
from [testTable]
where [uom] <> 'SQ' AND [branch] = '90001'

需求说明 #

针对branch = '90001'的记录，若某个itemcode在该分支下只有SQ这一种uom，则保留该记录；若有其他uom可选，则只保留uom <> 'SQ'的记录。

主表数据 #

原查询输出 #

期望输出 #

解决方案SQL #

SELECT [itemcode], [uom], [price], [branch]
FROM (
    SELECT 
        *,
        -- 统计当前itemcode在对应branch下的不同uom数量
        COUNT(DISTINCT [uom]) OVER (PARTITION BY [itemcode], [branch]) AS uom_count
    FROM [testTable]
    WHERE [branch] = '90001'
) AS filtered_data
WHERE 
    -- 保留非SQ的记录，或者该itemcode在分支下只有一种uom的记录
    ([uom] <> 'SQ') OR (uom_count = 1)

逻辑说明 #

1. 子查询中通过窗口函数COUNT(DISTINCT [uom]) OVER (PARTITION BY [itemcode], [branch])，统计每个itemcode在branch='90001'下的不同uom种类数，命名为uom_count。
2. 外层查询筛选时，满足两个条件之一即可：
   • 记录的uom不是SQ；
   • 该itemcode在当前分支下只有一种uom（无论是否为SQ都保留）。

这样就能精准匹配需求，得到期望的输出结果。

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