在 SQL 中有条件地查找重复的行

Question

问题
我需要有条件地过滤一组记录。筛选数据的规则是：按ProductNumber找到重复值，并且这些值需要具备至少两个ProductType(1和5)。

示例
产品

ProductNumber	ProductType	ProductName
A-000217	1	stuff 13
A-000217	5	stuff 13
A-000086	5	stuff 2
A-000086	5	stuff 2
A-001229	1	stuff 20
A-001146	5	stuff 21
A-001146	1	stuff 21

以下是过滤后的输出结果

产品 (Filtered)

产品编号	产品类型	产品名称
A-000217	1	物品 13
A-000217	5	物品 13
A-001146	5	物品 21
A-001146	1	物品 21

• 由于两个记录的产品 A-000086 均为类型 5，因此已将其排除。
• 只有一条记录的产品 A-001229 必须被排除。

我尝试了这段代码，但它没有按预期工作。

CREATE TABLE #Product(
    [Id] [int] IDENTITY(1,1)  NOT NULL,
    [ProductNumber] [varchar](60) NOT NULL,
    [ProductType] [int] NOT NULL,
    [ProductName] [varchar](60) NOT NULL,
PRIMARY KEY(Id)
);

INSERT INTO [#Product]([ProductNumber], [ProductType], [ProductName])
 VALUES('A-000217', 1, 'stuff 13')
,('A-000217', 5, 'stuff 13')
,('A-000086', 5, 'stuff 2')
,('A-000086', 5, 'stuff 2')
,('A-001229', 1, 'stuff 20')
,('A-001146', 5, 'stuff 21')
,('A-001146', 1, 'stuff 21')

--Filter data
; WITH CTE AS (
SELECT 
      ProductNumber 
    , ProductType 
    , ProductName
    , COUNT(ProductType) OVER(PARTITION BY ProductNumber) AS [RowNumber]
 FROM #Product
)
SELECT * FROM CTE WHERE RowNumber > 1

结果：

我的代码有什么问题？或者我该如何实现这种方法？

- bryannsi

---

根据问题指南，请勿发布代码、数据、错误消息等图片，而是将文本复制或输入到问题中。请保留使用图像来绘制图表或演示渲染错误等情况，这些事情无法准确地通过文本描述。 - Dale K

Answer 1

GROUP BY在CTE中使用HAVING，可以为每个产品编号和不同类型的总数提供您要搜索的结果。

WITH CTE AS
(SELECT
[ProductNumber], [ProductName]
FROM 
[#Product]
GROUP BY [ProductNumber], [ProductName] 
HAVING COUNT(*) > 1 AND COUNT(DISTINCT [ProductType]) > 1)
SELECT p1.[ProductNumber], p1.[ProductType], p1.[ProductName] 
FROM [#Product] p1 JOIN CTE p2 ON p1.[ProductNumber] = p2.[ProductNumber]

ProductNumber | ProductType | ProductName
:------------ | ----------: | :----------
A-000217      |           1 | stuff 13   
A-000217      |           5 | stuff 13   
A-001146      |           5 | stuff 21   
A-001146      |           1 | stuff 21   

db<>fiddle here

- nbk

Answer 2

另一种方法是使用exists的相关查询，这应该是高效的：

select * 
from #Product p
where exists (
    select * from #Product p2
    where p2.ProductNumber = p.ProductNumber
    group by p2.productNumber
    having Min(p2.productType) = 1 and Max(p2.productType) = 5
);

演示 DB<>Fiddle

- Stu

Answer 3

这不是太直观，但它有效。您希望 ProductNumberCount 大于或等于 2（即 1 和 5），但 ProductTypeCount 应为 1（每个 ProductNumber 只能有一个 1 和 5）。或许在 CTE 中只过滤 1 和 5 也不是坏主意。

;WITH CTE AS (
    SELECT 
          ProductNumber 
        , ProductType 
        , ProductName
        , COUNT(ProductNumber) OVER(PARTITION BY ProductNumber) AS [ProductNumberCount]
        , COUNT(ProductType) OVER(PARTITION BY ProductNumber, ProductType) AS [ProductTypeCount]
     FROM #Product
     WHERE ProductType IN (1, 5)
)
SELECT ProductNumber
    ,ProductType
    ,ProductName
FROM CTE WHERE CTE.ProductNumberCount >= 2 AND CTE.ProductTypeCount = 1

示例 这里

- Nenad