删除具有最早日期的重复行

Question

• 名
• Middlename
• lastname
• dob
• 地址
• 城市
• 状态
• 电话
• 验证日期

它有许多相同的行，除了有效日期。例如：

steve,s,smith, 19710909,112 crazy st,miami,fl,3055551212,201609
steve,s,smith, 19710909,112 crazy st,miami,fl,3055551212,201002
steve,s,smith, 19710909,112 crazy st,miami,fl,3055551212,201706
steve,s,smith, 19710909,112 crazy st,miami,fl,3055551212,199812

我希望运行一个脚本，删除除最后一列(validitydate)以外的所有匹配的重复项，只在表中保留以下内容，这是201706:

steve,s,smith, 19710909,112 crazy st,miami,fl,3055551212,201706

DELETE 
FROM PF_temp
LEFT OUTER JOIN 
(
    SELECT Min(ValidityDate) as RowId
    , firstname
    , middlename
    , lastname
    , DOB
    , address
    , city
    , state 
    , phone
    FROM PF_temp 
    GROUP BY firstname
    , middlename
    , lastname
    , DOB
    , address
    , city
    , state 
    , phone
    , validitydate 
) as KeepRows 
ON TableName.RowId = KeepRows.RowId
WHERE KeepRows.RowId IS NULL

它不起作用，实际上弹出以下错误：

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OUTER'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'as'.

另外，我想根据字母表的最后一个字母分阶段运行它。因此，需要在某个地方添加类似where'a%'这样的lastname。

Answer 1

试试这个：

delete a
from PF_Temp a
inner join PF_Temp b 
on  b.firstname = a.firstname 
and b.middlename = a.middlename
and b.lastname = a.lastname
and b.DOB = a.DOB
and b.address = a.address
and b.city = a.city
and b.state = a.state
and b.phone = a.phone
and b.validitydate > a.validitydate

SQL Fiddle中的示例。

以上作品由：

null

delete a
from PF_Temp a
inner join PF_Temp b 
on  ((b.firstname = a.firstname) or (b.firstname is null and a.firstname is null))
and ((b.middlename = a.middlename) or (b.middlename is null and a.middlename is null))
and ((b.lastname = a.lastname) or (b.lastname is null and a.lastname is null))
and ((b.DOB = a.DOB) or (b.DOB is null and a.DOB is null))
and ((b.address = a.address) or (b.address is null and a.address is null))
and ((b.city = a.city) or (b.city is null and a.city is null))
and ((b.state = a.state) or (b.state is null and a.state is null))
and ((b.phone = a.phone) or (b.phone is null and a.phone is null))
and b.validitydate > a.validitydate

上述方法的另一种选择是coalesce(b.firstname，'')=coalesce(a.firstname)上的（对所有其他匹配字段重复该模式）；尽管这意味着空值和空值的处理是一样的，并且不会表现得很好。

替代方法

另一种方法是使用一个子查询回调所有值，用匹配的值对每个集合进行编号，从最近的有效日期1开始。然后删除所有返回的数字高于1的行；即任何有效日期较早的副本。

delete TheDeletables
from 
(
    select *
    , row_number() over (
        partition by 
         firstname 
        , middlename 
        , lastname 
        , DOB 
        , address  
        , city 
        , state 
        , phone 
        order by validitydate desc
    ) rowid
    from PF_Temp
) TheDeletables
where rowid > 1;