我使用的是SQL Server2008 R2。
我在这个脚本中发现了重复的行:
SELECT CLDest, CdClient,
COUNT(CLDest) AS NumOccurrences
FROM DEST
GROUP BY CLDest,CdClient
HAVING ( COUNT(CLDest) > 1 )它返回48个条目
在我删除之前,我必须确保我删除了替身:
SELECT DEST.CdClient
,DEST.CLDest
FROM [Soft8Exp_Client_WEB].[dbo].[DEST]
WHERE DEST.CdClient IN (SELECT CdClient
FROM DEST
GROUP BY CdClient
HAVING (COUNT(CLDest) > 1) )
AND DEST.CLDest IN (SELECT CLDest
FROM DEST
GROUP BY CLDest
HAVING (COUNT(CLDest) > 1) )此查询返回64628个条目
所以我想我的选择是错误的。
发布于 2014-03-04 00:03:23
SQL Server具有可更新CTE的良好属性。当与函数row_number()结合使用时,它会执行您想要的操作:
with todelete as (
select d.*,
row_number() over (partition by CLDest, CdClient order by newid()) as seqnum
from dest d
)
delete from todelete
where seqnum > 1;此版本将随机删除其中一个重复项。它所做的是为具有相同值的行分配一个序列号,并删除除第一个找到的行之外的所有行。如果您想按日期保存某些内容,那么在order by中使用不同的表达式。
发布于 2014-03-04 00:02:40
;WITH Duplicates
AS
(
SELECT CLDest
, CdClient
, ROW_NUMBER() OVER (PARTITION BY CLDest, CdClient ORDER BY CdClient) AS Rn
FROM DEST
)
DELETE FROM Duplicates
WHERE RN > 1发布于 2014-03-04 00:06:33
SELECT DEST.CdClient,DEST.CLDest
FROM [Soft8Exp_Client_WEB].[dbo].[DEST]
WHERE DEST.CdClient+DEST.CLDest
IN (
SELECT CdClient+CLDest FROM DEST GROUP BY CLDest HAVING ( COUNT(CLDest) > 1 )
)https://stackoverflow.com/questions/22151299
复制相似问题