none
SQL2005 如何删除数据库表中重复的数据 RRS feed

  • 问题

  • 有数据库内表A 如下

     NAME                                      TIME                                    MO                              NE

      3274A                              2010-08-11  09:00:00                   21                            345

      3274A                              2010-08-11  09:00:00                   21                            345

      3274A                              2010-08-11  10:00:00                   26                            213

      3274A                              2010-08-11  11:00:00                   23                           324

    如何删除重复数据 保留如下结果

    NAME                                      TIME                                    MO                              NE

      3274A                              2010-08-11  09:00:00                   21                            345

      3274A                              2010-08-11  10:00:00                   26                            213

      3274A                              2010-08-11  11:00:00                   23                           324

    2010年8月12日 5:25

答案

  • delete test from ( select name, time, mo, ne, ROW_NUMBER() over (partition by name, time, mo, ne order by name) as row_num from A ) test WHERE test.row_num > 1
    • 已标记为答案 耶穆 2010年8月12日 11:54
    2010年8月12日 6:41
  • There are more than 3 solutions from which you could choose depending on your situation:

    1. SELECT DISTINCT * INTO dbo.OrdersTmp FROM dbo.OrdersDups;

    2. DELETE FROM dbo.OrdersDups
    WHERE EXISTS
      (SELECT *
       FROM dbo.OrdersDups AS O2
       WHERE O2.OrderID = dbo.OrdersDups.OrderID
         AND O2.KeyCol > dbo.OrdersDups.KeyCol);

    3. WITH Dups AS
    (
      SELECT *,
        ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderID) AS rn
      FROM dbo.OrdersDups
    )
    DELETE FROM Dups WHERE rn > 1;

    (无欲则刚,大智弱愚采用的就是第三种办法)

    Hope this helps.


    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    • 已标记为答案 耶穆 2010年8月12日 11:54
    2010年8月12日 8:55
  • --1.导入其他表 删除原表 改名
    select distinct * into kz_a from a;
    drop table a;
    exec sp_rename 'kz_a','a';
    go
    
    --2.cte
    ;with cte as
    (
    	select *,rn=row_number()over(partition by name,time,mo,ne order by getdate())
    	from a
    )
    delete cte 
    where rn<>1
    
    
    • 已标记为答案 耶穆 2010年8月12日 11:54
    2010年8月12日 11:16

全部回复

  • delete test from ( select name, time, mo, ne, ROW_NUMBER() over (partition by name, time, mo, ne order by name) as row_num from A ) test WHERE test.row_num > 1
    • 已标记为答案 耶穆 2010年8月12日 11:54
    2010年8月12日 6:41
  • There are more than 3 solutions from which you could choose depending on your situation:

    1. SELECT DISTINCT * INTO dbo.OrdersTmp FROM dbo.OrdersDups;

    2. DELETE FROM dbo.OrdersDups
    WHERE EXISTS
      (SELECT *
       FROM dbo.OrdersDups AS O2
       WHERE O2.OrderID = dbo.OrdersDups.OrderID
         AND O2.KeyCol > dbo.OrdersDups.KeyCol);

    3. WITH Dups AS
    (
      SELECT *,
        ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderID) AS rn
      FROM dbo.OrdersDups
    )
    DELETE FROM Dups WHERE rn > 1;

    (无欲则刚,大智弱愚采用的就是第三种办法)

    Hope this helps.


    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    • 已标记为答案 耶穆 2010年8月12日 11:54
    2010年8月12日 8:55
  • --1.导入其他表 删除原表 改名
    select distinct * into kz_a from a;
    drop table a;
    exec sp_rename 'kz_a','a';
    go
    
    --2.cte
    ;with cte as
    (
    	select *,rn=row_number()over(partition by name,time,mo,ne order by getdate())
    	from a
    )
    delete cte 
    where rn<>1
    
    
    • 已标记为答案 耶穆 2010年8月12日 11:54
    2010年8月12日 11:16
  • 感谢大家
    2010年8月12日 11:54