查询在一列中找到三个相同的实例，但在另一列中必须有三个不同的结果

Question

我有一个像这样的表:

InspectDate | Serial Number | Reference | Error | PartNumber

我需要找到最近10天发生的错误数据。我可以得到这个，但是我只需要找到那些发生在同一参考上的问题，但只有当它们碰巧在三个或更多不同的序列号上。

如果我需要提供更多的信息，请告诉我。我已经尝试使用计数和过滤那些超过3，但这只显示我任何一个序列号有超过三个错误的参考。

样本数据:

InspectDate     SerialNumber    Reference   Error   PartNumber
Oct 12 2021  1:58PM 50012       A21     1   PL2-001
Oct 12 2021  3:22PM 50013       A21     1   PL2-001
Oct 12 2021  5:59PM 50062       A21     1   PL2-001
Oct 18 2021 11:24AM 50071       A21     1   PL2-001
Oct 18 2021 12:20PM 50071       A21     2   PL2-001
Oct 18 2021 12:36PM 50071       A21     3   PL2-001
Oct 12 2021  5:59PM 50055       B44     5   AL1-440
Oct 18 2021 11:19AM 50062       B72     1   AL1-660
Oct 18 2021 11:22AM 50071       B72     2   AL1-660
Oct 12 2021  5:39PM 50047       B83     5   AL1-550
Oct 12 2021  3:03PM 50013       V310        2   PL3-010
Oct 18 2021 12:00PM 50071       V310        2   PL3-010
Oct 18 2021 12:37PM 50098       V310        4   PL3-010

预期结果:

InspectDate     SerialNumber    Reference   Error   PartNumber
Oct 12 2021 1:58PM  50012       A21     1   PL2-001
Oct 12 2021 3:22PM  50013       A21     1   PL2-001
Oct 12 2021 5:59PM  50062       A21     1   PL2-001
Oct 18 2021 11:24AM 50071       A21     1   PL2-001
Oct 12 2021 3:03PM  50013       V310        2   PL3-010
Oct 18 2021 12:00PM 50071       V310        2   PL3-010
Oct 18 2021 12:37PM 50098       V310        4   PL3-010

诱惑代码:

Select (all columns), COUNT() AS Instances From (Table) 
Where InspectDate >= DATEADD(day, -10, GETDATE()) 
GROUP BY (all columns) 
HAVING COUNT() >= 3 
Order by CAST (inspectdate as datetime) DESC

Answer 1

您需要的是一个窗口COUNT(DISTINCT。遗憾的是，SQL Server不允许COUNT(DISTINCT作为窗口函数。

但我们可以用DENSE_RANK和MAX作为窗函数来模拟

WITH Ranked AS (
SELECT *,
rn = DENSE_RANK() OVER (PARTITION BY Reference ORDER BY SerialNumber)
FROM [Table]
WHERE InspectDate >= DATEADD(day, -10, GETDATE()) 
),
DistinctCount AS (
SELECT *,
maxrn = MAX(rn) OVER (PARTITION BY Reference)
FROM Ranked
)
SELECT *
FROM DistinctCount
WHERE maxrn >= 3;