SQL筛选表中出现≥3次的item_number，查询返回空如何解决

我需要筛选并展示所有出现次数至少3次的item_number，已编写的SQL查询如下：

SELECT row_number() over(partition by i.item_number order by i.item_number) row_num 
    ,i.item_number 
    ,sto.location_id
    ,sto.actual_qty
    ,trn.source_location_id
    ,trn.tran_qty
FROM #items i
LEFT JOIN (select loc.location_wh, sto.item_number, sto.location_id, sto.actual_qty, sto.status from t_stored_item sto WITH (NOLOCK)
        INNER JOIN t_location loc WITH (NOLOCK)
        on loc.location_id = sto.location_id 
        where loc.location_wh = '80'
        and loc.type = 'I') sto
ON sto.item_number = i.item_number 
LEFT JOIN (select t.item_number, t.source_location_id, sum(t.tran_qty) tran_qty from t_tran_log t WITH (NOLOCK)
        WHERE t.tran_type = '305'
        and convert(date, end_tran_date AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') = '2021-11-16'
        GROUP BY t.item_number, t.source_location_id) trn
on trn.item_number = i.item_number
and trn.source_location_id = sto.location_id
WHERE sto.location_id IS NOT NULL

查询得到的数据如下：

row_num item_number location_id actual_qty source_location_id tran_qty
1   1040370 AL-27-03-A  120 NULL    NULL
2   1040370 BH-16-04-A  96  NULL    NULL
1   1089630 BV-59-04-F  192 NULL    NULL
2   1089630 BW-35-05-D  96  NULL    NULL
3   1089630 BQ-28-04-A  576 NULL    NULL
1   1132345 BZ-32-07-C  804 NULL    NULL
2   1132345 AG-51-02-B  588 NULL    NULL
3   1132345 BX-28-08-C  60  NULL    NULL
4   1132345 AH-18-06-B  600 NULL    NULL
5   1132345 BX-14-04-C  108 NULL    NULL

现在我想要筛选出所有出现至少3次的item_number，比如样例中的1089630满足条件应该被返回，我尝试用CTE嵌套编写了如下查询，但返回结果为空：

;with temp as (
SELECT row_number() over(partition by i.item_number order by i.item_number) row_num 
    ,i.item_number 
    ,sto.location_id
    ,sto.actual_qty
    ,trn.source_location_id
    ,trn.tran_qty
FROM #items i
LEFT JOIN (select loc.location_wh, sto.item_number, sto.location_id, sto.actual_qty, sto.status from t_stored_item sto WITH (NOLOCK)
        INNER JOIN t_location loc WITH (NOLOCK)
        on loc.location_id = sto.location_id 
        where loc.location_wh = '80'
        and loc.type = 'I') sto
ON sto.item_number = i.item_number 
LEFT JOIN (select t.item_number, t.source_location_id, sum(t.tran_qty) tran_qty from t_tran_log t WITH (NOLOCK)
        WHERE t.tran_type = '305'
        and convert(date, end_tran_date AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') = '2021-11-16'
        GROUP BY t.item_number, t.source_location_id) trn
on trn.item_number = i.item_number
and trn.source_location_id = sto.location_id
WHERE sto.location_id IS NOT NULL
--ORDER BY item_number 
)

select row_num, item_number, location_id, actual_qty, source_location_id, tran_qty 
from temp 
GROUP BY row_num, item_number, location_id, actual_qty, source_location_id, tran_qty
having count(item_number) >= 3

错误原因 #

你将所有查询字段都放进了GROUP BY子句，相当于每一行数据都是一个独立分组，每个分组的count(item_number)结果只会是1，永远达不到≥3的判断条件，因此返回空。

正确修改方案 #

使用窗口函数统计每个item_number的总出现次数，直接过滤符合条件的记录即可，不需要额外分组，能保留所有符合条件item的全部明细行：

;with temp as (
SELECT 
    row_number() over(partition by i.item_number order by i.item_number) row_num,
    -- 新增窗口函数统计每个item_number的总出现次数
    count(1) over(partition by i.item_number) as item_total_count,
    i.item_number,
    sto.location_id,
    sto.actual_qty,
    trn.source_location_id,
    trn.tran_qty
FROM #items i
LEFT JOIN (
    select loc.location_wh, sto.item_number, sto.location_id, sto.actual_qty, sto.status 
    from t_stored_item sto WITH (NOLOCK)
    INNER JOIN t_location loc WITH (NOLOCK)
    on loc.location_id = sto.location_id 
    where loc.location_wh = '80'
    and loc.type = 'I'
) sto ON sto.item_number = i.item_number 
LEFT JOIN (
    select t.item_number, t.source_location_id, sum(t.tran_qty) tran_qty 
    from t_tran_log t WITH (NOLOCK)
    WHERE t.tran_type = '305'
    and convert(date, end_tran_date AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') = '2021-11-16'
    GROUP BY t.item_number, t.source_location_id
) trn on trn.item_number = i.item_number
and trn.source_location_id = sto.location_id
WHERE sto.location_id IS NOT NULL
)
select row_num, item_number, location_id, actual_qty, source_location_id, tran_qty 
from temp 
where item_total_count >= 3

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