SQL Server技术问询：筛选派生表中某列值重复次数>1的行

嘿，我来帮你解决这个筛选重复Name的问题！你需要从合并Transfer和TransferClosed表的结果里，找出Name列出现次数超过1次的行，这里有两种实用的方法：

如果你的数据库支持窗口函数（比如PostgreSQL、SQL Server、MySQL 8.0+等），这是最直接的方案。我们先合并两个表并筛选日期，再用COUNT() OVER(PARTITION BY Name)计算每个Name的出现次数，最后只保留次数大于1的行：

SELECT Payer, PaymentDate, Name
FROM (
    SELECT 
        Payer, 
        PaymentDate, 
        Name,
        COUNT(*) OVER(PARTITION BY Name) AS NameCount
    FROM (
        -- 合并两个表的基础数据
        SELECT 
            T.InstitutionRoleXrefLongName AS 'Payer', 
            T.PaymentDate AS 'PaymentDate', 
            T.FullName AS 'Name'
        FROM Transfer T
        UNION ALL
        SELECT 
            T.InstitutionRoleXrefLongName AS 'Payer', 
            T.PaymentDate AS 'PaymentDate', 
            T.FullName AS 'Name'
        FROM TransferClosed T
    ) CombinedData
    -- 先筛选日期范围，减少后续计算量
    WHERE PaymentDate BETWEEN '20180101' AND '20180331 23:59:59'
) FilteredResults
-- 只保留重复次数超过1次的行
WHERE NameCount > 1;

如果你的数据库不支持窗口函数（比如MySQL 5.x及更早版本），可以先通过GROUP BY找出重复的Name，再和合并后的结果关联，得到完整的行数据：

SELECT c.Payer, c.PaymentDate, c.Name
FROM (
    -- 合并两个表并筛选日期
    SELECT 
        T.InstitutionRoleXrefLongName AS 'Payer', 
        T.PaymentDate AS 'PaymentDate', 
        T.FullName AS 'Name'
    FROM Transfer T
    WHERE PaymentDate BETWEEN '20180101' AND '20180331 23:59:59'
    UNION ALL
    SELECT 
        T.InstitutionRoleXrefLongName AS 'Payer', 
        T.PaymentDate AS 'PaymentDate', 
        T.FullName AS 'Name'
    FROM TransferClosed T
    WHERE PaymentDate BETWEEN '20180101' AND '20180331 23:59:59'
) c
-- 关联重复的Name列表
JOIN (
    SELECT Name
    FROM (
        SELECT FullName AS Name
        FROM Transfer T
        WHERE PaymentDate BETWEEN '20180101' AND '20180331 23:59:59'
        UNION ALL
        SELECT FullName AS Name
        FROM TransferClosed T
        WHERE PaymentDate BETWEEN '20180101' AND '20180331 23:59:59'
    ) AllNames
    GROUP BY Name
    -- 筛选出现次数超过1次的Name
    HAVING COUNT(*) > 1
) DuplicateNames ON c.Name = DuplicateNames.Name;

小提示 #

• 方法1的性能更好，因为只需要扫描一次合并后的数据集；
• 两种方法都先做了日期筛选，避免处理不必要的数据，提升效率；
• 注意UNION ALL会保留重复行，这正是我们需要的，因为要统计Name的真实出现次数。

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