如何删除没有唯一行id存在的重复行?
我的座位是
col1 col2 col3 col4 col5 col6 col7
john 1 1 1 1 1 1
john 1 1 1 1 1 1
sally 2 2 2 2 2 2
sally 2 2 2 2 2 2
我想留下以下重复删除后:
john 1 1 1 1 1 1
sally 2 2 2 2 2 2
我尝试了一些查询，但我认为他们取决于有一个行id，因为我没有得到想要的结果。例如:
DELETE
FROM table
WHERE col1 IN (
SELECT id
FROM table
GROUP BY id
HAVING (COUNT(col1) > 1)
)
当前回答
-- this query will keep only one instance of a duplicate record.
;WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2, col3-- based on what? --can be multiple columns
ORDER BY ( SELECT 0)) RN
FROM Mytable)
delete FROM cte
WHERE RN > 1
其他回答
参考https://support.microsoft.com/en-us/help/139444/how-to-remove-duplicate-rows-from-a-table-in-sql-server
删除重复的想法涉及
a)保护那些不重复的行 b)保留众多符合条件的重复行中的一行。
循序渐进的
1)首先确定满足重复定义的行 并将它们插入到临时表中，写入#tableAll。 2)选择不重复的(单行)或不同的行到临时表中 # tableUnique说。 3)从源表中删除连接#tableAll来删除 重复。 4)插入源表中所有来自#tableUnique的行。 5)删除#tableAll和#tableUnique
DELETE from search
where id not in (
select min(id) from search
group by url
having count(*)=1
union
SELECT min(id) FROM search
group by url
having count(*) > 1
)
您需要根据字段对重复的记录进行分组，然后保留其中一条记录并删除其余记录。 例如:
DELETE prg.Person WHERE Id IN (
SELECT dublicateRow.Id FROM
(
select MIN(Id) MinId, NationalCode
from prg.Person group by NationalCode having count(NationalCode ) > 1
) GroupSelect
JOIN prg.Person dublicateRow ON dublicateRow.NationalCode = GroupSelect.NationalCode
WHERE dublicateRow.Id <> GroupSelect.MinId)
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
从一个巨大的(几百万条记录)表中删除重复项可能需要很长时间。我建议将所选行的批量插入到临时表中，而不是删除。
--REWRITING YOUR CODE(TAKE NOTE OF THE 3RD LINE) WITH CTE AS(SELECT NAME,ROW_NUMBER()
OVER (PARTITION BY NAME ORDER BY NAME) ID FROM @TB) SELECT * INTO #unique_records FROM
CTE WHERE ID =1;
