sql-server - 仅在具有 3 个或更多结果的项目时显示

Question

我需要你的帮助。我只需要在查询中显示具有 3 个或更多结果的项目

这条 SQL 语句只获取每个项目的类型。

select INUMBR as ITEM, IUPC as BARCODE, IUPCCD as Type
from [DWSTAGE].[INVUPC] 
where iupccd = 'X'
order by INUMBR

这是我的查询结果：

ITEM          | BARCODE             | TYPE  
--------------+---------------------+----------
12458         |  481181880922       |   X    
12458         |  481181880924       |   X    
12458         |  481181880926       |   X    
30689         |  485481881862       |   X    
30699         |  485481881861       |   X       
11449         |  483981889371       |   X    
44775         |  480081880924       |   X    
44775         |  480081880922       |   X 
44775         |  480081880923       |   X    
44775         |  480081880925       |   X 

我的预期结果：它应该只显示具有 3 个或更多结果的项目：

ITEM          | BARCODE             | TYPE  
--------------+---------------------+---------
12458         |  481181880922       |   X    
12458         |  481181880924       |   X    
12458         |  481181880926       |   X       
44775         |  480081880924       |   X    
44775         |  480081880922       |   X 
44775         |  480081880923       |   X    
44775         |  480081880925       |   X 

标签： sql-server

Answer 1

一种方法COUNT用作分析函数：

WITH cte AS (
    SELECT INUMBR AS ITEM, IUPC AS BARCODE, IUPCCD AS TYPE,
           COUNT(*) OVER (PARTITION BY INUMBR) ITEM_CNT
    FROM [DWSTAGE].[INVUPC] 
    WHERE iupccd = 'X'
)

SELECT ITEM, BARCODE, TYPE
FROM cte
WHERE ITEM_CNT >= 3
ORDER BY BARCODE;