sql - 从 Select 语句到 Delete

Question

我有这个查询，我发现它有重复的记录:

SELECT x.[Constituent Number], x.[First Name], x.[Last or Org Name],x.[Address Line 1], Cnt = Count(*) 
FROM (   
    SELECT SR.[Constituent Number], SR.[First Name], SR.[Last or Org Name], SR.[Address Line 1]
    FROM dbo.['All Salsa Records TL$'] SR

    UNION ALL

    SELECT WD.[Member Number], WD.[First Name], wd.[Last or Org Name], WD.[Address Line 1]
    FROM dbo.['Workling to delete$'] WD
) x
GROUP BY x.[Constituent Number], x.[First Name],x.[Last or Org Name],x.[Address Line 1]
HAVING COUNT(*) > 1

这是在两个表之间。我将如何从主表中删除记录以匹配该查询选择的结果？

编辑:我设法用这段代码弄明白了:

DELETE SR
--SELECT SR.[Constituent Number], SR.[First Name], SR.[Last or Org Name]
FROM dbo.['All Salsa Records TL$'] AS SR
LEFT OUTER JOIN dbo.['Workling to delete$'] WD
    ON WD.[Member Number] = SR.[Constituent Number]
    AND WD.[First Name] = SR.[First Name]
    AND WD.[Last or Org Name] = SR.[Last or Org Name] 
    AND WD.[Address Line 1] = SR.[Address Line 1]
    AND WD.[City]  = SR.[City]
    AND WD.[State] = SR.[State]
WHERE SR.[Constituent Number] = WD.[Member Number]
    AND WD.[First Name] = SR.[First Name]
    AND WD.[Last or Org Name] = SR.[Last or Org Name] 
    AND WD.[Address Line 1] = SR.[Address Line 1]
    AND WD.[City]  = SR.[City]
    AND WD.[State] = SR.[State]

对于空值，我只是使用了它(并相应地进行了更改，因为我有 11 条包含空成分的记录和 26 条包含空地址的记录):

DELETE SR
--SELECT SR.[Constituent Number], SR.[First Name], SR.[Last or Org Name]
FROM dbo.['All Salsa Records TL$'] AS SR
LEFT OUTER JOIN dbo.['Workling to delete$'] WD
    ON WD.[Member Number] = SR.[Constituent Number]
    AND WD.[First Name] = SR.[First Name]
    AND WD.[Last or Org Name] = SR.[Last or Org Name] 
    AND WD.[Address Line 1] IS NULL
    AND WD.[City] IS NULL
    AND WD.[State] IS NULL
WHERE SR.[Constituent Number] = WD.[Member Number]
    AND WD.[First Name] = SR.[First Name]
    AND WD.[Last or Org Name] = SR.[Last or Org Name] 
    AND SR.[Address Line 1] IS NULL
    AND SR.[City] IS NULL
    AND SR.[State] IS NULL

Answer 1

如果 ['All Salsa Records TL$'] 是您的主表并且 [Constituent Number] 是您要删除行的列，则:

with cte as(
SELECT x.[Constituent Number], x.[First Name], x.[Last or Org Name],x.[Address Line 1], Cnt = Count(*) 
FROM (   
    SELECT SR.[Constituent Number], SR.[First Name], SR.[Last or Org Name], SR.[Address Line 1]
    FROM dbo.['All Salsa Records TL$'] SR

    UNION ALL

    SELECT WD.[Member Number], WD.[First Name], wd.[Last or Org Name], WD.[Address Line 1]
    FROM dbo.['Workling to delete$'] WD
) x
GROUP BY x.[Constituent Number], x.[First Name],x.[Last or Org Name],x.[Address Line 1]
HAVING COUNT(*) > 1)
delete dbo.['All Salsa Records TL$'] where [Constituent Number]  in (select [Constituent Number] from cte)