SQL Server 2012中用Case+DENSE_RANK统计指定优先级唯一Item_id

原始数据 #

comp_id      Item_id      Priority
123456      123          1
123456      321          1
123456      333          2
123456      334          3
123456      334          3
123456      337          4
123457      339          1

需求 #

按comp_id分组，统计每组内Priority为1或2的唯一Item_id数量，且该组所有行（包括Priority=3/4的行）都要显示这个统计值。

问题分析 #

你当前的SQL语句仅在Priority=1/2的行生成统计值，其他行返回NULL，核心原因是CASE语句限制了窗口函数的作用范围，未覆盖所有行的统计逻辑。

解决方案 #

方案1：用COUNT(DISTINCT)窗口函数（简洁高效） #

SQL Server 2012及以上支持在窗口函数中使用COUNT(DISTINCT)，直接通过条件统计实现需求：

SELECT
    comp_id,
    Item_id,
    Priority,
    COUNT(DISTINCT CASE WHEN Priority IN (1, 2) THEN Item_id END) 
        OVER (PARTITION BY comp_id) AS distinct_count
FROM your_table;

方案2：基于DENSE_RANK实现（满足指定函数要求） #

如果必须使用DENSE_RANK，可以先对符合条件的Item_id排名，再提取每组的最大排名值作为统计数：

WITH cte AS (
    SELECT
        comp_id,
        Item_id,
        Priority,
        -- 仅对Priority=1/2的Item_id进行排名，其他行排名为NULL
        DENSE_RANK() OVER (PARTITION BY comp_id ORDER BY CASE WHEN Priority IN (1,2) THEN Item_id END) AS dense_rank_val
    FROM your_table
)
SELECT
    comp_id,
    Item_id,
    Priority,
    -- 取每组内Priority=1/2行的最大排名，即为唯一Item_id数量
    MAX(dense_rank_val) OVER (PARTITION BY comp_id) AS distinct_count
FROM cte;

预期输出 #

两种方案都会得到以下结果：

comp_id      Item_id      Priority     distinct_count
123456      123          1             3
123456      321          1             3
123456      333          2             3
123456      334          3             3
123456      334          3             3
123456      337          4             3
123457      339          1             1

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