sql - 使用 SQL 查找数据组中的缺失值

Question

最近我需要根据事实生成一份报告:

TableA 有以下 2 列 UserID 和 DocumentType

我已获得“强制”文档类型列表:Type1、Type2、Type3，我需要返回不具有所有这三种类型的每个 UserID 以及它们缺少的类型。

例如，如果 TableA 包含以下行

12  Type1
12  Type2
12  Type4
13  Type1
13  Type2
13  Type3
14  Type1
15  Type6
15  Type7
15  Type8

那么理想的输出将是这样的:

12 Type3
14 Type2, Type3
15 Type1, Type2, Type3

理想情况下，生成结果的查询应该能够处理多达数千万条记录。

我们最近使用 SQL Server 2012 实现了一个类似问题(比这个复杂一点)的解决方案。需要 3 分半钟才能在总共大约 400 万条记录的多个表中获得完整的报告。我们想知道是否有更好的想法可以更快地做到这一点。

请随时分享您可以解决此问题的想法。

谢谢! :)

Answer 1

<强> DEMO

WITH 
base ([DocumentType]) as (
    SELECT 'Type1' UNION ALL
    SELECT 'Type2' UNION ALL
    SELECT 'Type3'
),
users as (
    SELECT DISTINCT [userID]
    FROM Table1 t    
),
pairs as ( 
    SELECT *
    FROM users, base
)    
SELECT p.userID, p.[DocumentType], t.[DocumentType]
FROM pairs p 
LEFT JOIN Table1 t
       ON p.[DocumentType] = t.[DocumentType]
      AND p.[userID] = t.[userID]
WHERE t.[DocumentType] IS NULL   

输出