在SQL Server中删除重复数据并加载到另一个表中

Question

我有一个关于SQL Server的问题。

表格：emp

empid   |  name |sal
1       |  abc  |100
2       |  def  |200
3       |  test |300
2       |  har  |500
3       |  jai  |600
4       | kali  |240

根据上面的表格，这张表格有重复数据，我想从 emp 表中删除重复数据。

并且要将重复数据加载到 empduplicate 表中。

这里 empid 是唯一的。如果 empid 出现了多次，则该记录被视为重复。

empduplicate 的结构如下：

Empid   |  name  | sal

在删除重复数据后，我希望看到emp表中的数据如下所示：

empid  |  name  | sal 
1      |  abc   | 100
4      | kali   | 240

为了删除重复项，我尝试了这段代码：

;with duplicate as 
(
    select 
        *,
        row_number()over (partition by empid order by empid) as rn
    from emp
)
delete from duplicate 
where rn > 1

但我无法删除整个记录。

例如：empid=2存在重复数据。

empid|name |sal
2    |def  |200
2    |har  |500

我需要删除empid=2对应的所有记录。因为empid=2有重复，所以需要从emp表中删除它。

并且empduplicate表需要加载重复数据，看起来像这样：

empid    | name   |sal
2        |def     |200
2        |har     |500
3        |test    |300
3        |jai     |600

对于插入重复数据，我尝试了以下代码：

insert into empduplicate 
    select 
        id, name, sal 
    from 
         emp  
    group by 
         id 
    having 
         count(*) > 1

执行该查询时会出现错误:

'duplicate.name'列无效，因为它既没有包含在聚合函数中也没有包含在GROUP BY子句中。

请告诉我如何编写SQL Server查询以实现我的任务。

- ravi

Answer 1

你已经快完成了。不要使用ROW_NUMBER，而是使用COUNT：

WITH CteInsert AS(
    SELECT *,
        cnt = COUNT(empid) OVER(PARTITION BY empid)
    FROM emp
)
INSERT INTO empduplicate(empid, name, sal)
SELECT
    empid, name, sal
FROM CteInsert
WHERE cnt > 1;

WITH CteDelete AS(
    SELECT *,
        cnt = COUNT(empid) OVER(PARTITION BY empid)
    FROM emp
)
DELETE FROM CteDelete WHERE cnt > 1;

在执行DELETE之前，你需要先执行INSERT。另外，你可能想把这些操作放在一个单独的事务中。

- Felix Pamittan

Answer 2

BEGIN TRAN
SELECT * INTO empduplicate FROM
(
 SELECT * 
 FROM emp
 WHERE empid IN (
  SELECT empid FROM emp 
  GROUP BY empid
  HAVING COUNT(empid)>1
 )
) as M

DELETE FROM emp WHERE empid IN (
 SELECT empid FROM emp
 GROUP BY empid
 HAVING COUNT(empid)>1
)

COMMIT TRAN

- BalajiShriram

Answer 3

SELECT DISTINCT * INTO #tmp FROM emp
 DELETE FROM emp
INSERT INTO emp
SELECT * FROM #tmp DROP table #tmp

SELECT * FROM emp ---------------------------- All Distinct ID

SELECT * INTO #tmp FROM emp
WHERE empid in(
    SELECT empid FROM emp 
    group by empid having count(*) = 1
)
DELETE FROM emp
INSERT INTO emp
SELECT * FROM #tmp DROP table #tmp

SELECT * FROM emp ----------------------------All ID which is not duplicate

INSERT INTO empduplicate  
  SELECT * FROM emp where empid in(
    SELECT empid FROM emp 
    group by empid having count(*) >1
)

SELECT * FROM empduplicate  -------------------ALL Duplicate value.

- Amee