sql-server - one - sql group by




我如何刪除重複的行? (20)

從應用程序級別(不幸)。 我同意防止重複的正確方法是通過使用唯一索引在數據庫級別,但在SQL Server 2005中,索引只允許為900個字節,而我的varchar(2048)字段會將此消除。

我不知道它的表現會如何,但我認為你可以編寫一個觸發器來強制執行,即使你不能直接用索引來實現。 就像是:

-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism 
ON stories 
after INSERT, UPDATE 
AS 
    DECLARE @cnt AS INT 

    SELECT @cnt = Count(*) 
    FROM   stories 
           INNER JOIN inserted 
                   ON ( stories.story = inserted.story 
                        AND stories.story_id != inserted.story_id ) 

    IF @cnt > 0 
      BEGIN 
          RAISERROR('plagiarism detected',16,1) 

          ROLLBACK TRANSACTION 
      END 

另外,varchar(2048)聽起來很腥(生活中有些東西是2048字節,但很少見); 它應該不是varchar(max)?

從相當大的SQL Server表中刪除重複行的最佳方法是什麼(即300,000多行)?

由於RowID標識字段的存在,行當然不會是完美的重複項。

MyTable的

RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

  1. 用相同的結構創建新的空白表

  2. 像這樣執行查詢

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) > 1
    
  3. 然後執行這個查詢

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) = 1
    

以下查詢對刪除重複行很有用。 此示例中的表具有ID作為標識列,具有重複數據的列是列1,列2和列Column3

DELETE FROM TableName
WHERE  ID NOT IN (SELECT MAX(ID)
                  FROM   TableName
                  GROUP  BY Column1,
                            Column2,
                            Column3
                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
                    nullable. Because of semantics of NOT IN (NULL) including the clause
                    below can simplify the plan*/
                  HAVING MAX(ID) IS NOT NULL) 

以下腳本在一個查詢中顯示GROUP BYHAVINGORDER BY用法,並返回包含重複列和計數的結果。

SELECT YourColumnName,
       COUNT(*) TotalCount
FROM   YourTableName
GROUP  BY YourColumnName
HAVING COUNT(*) > 1
ORDER  BY COUNT(*) DESC 

使用CTE。 這個想法是加入一個或多個組成重複記錄的列,然後刪除你喜歡的任何一個:

;with cte as (
    select 
        min(PrimaryKey) as PrimaryKey
        UniqueColumn1,
        UniqueColumn2
    from dbo.DuplicatesTable 
    group by
        UniqueColumn1, UniqueColumn1
    having count(*) > 1
)
delete d
from dbo.DuplicatesTable d 
inner join cte on 
    d.PrimaryKey > cte.PrimaryKey and
    d.UniqueColumn1 = cte.UniqueColumn1 and 
    d.UniqueColumn2 = cte.UniqueColumn2;

另一個簡單的解決方案可以在http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server粘貼的鏈接http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server 。 這個很容易理解,似乎對大多數類似問題都有效。 它雖然是SQL Server,但所使用的概念是可以接受的。

以下是鏈接頁面的相關部分:

考慮這些數據:

EMPLOYEE_ID ATTENDANCE_DATE
A001    2011-01-01
A001    2011-01-01
A002    2011-01-01
A002    2011-01-01
A002    2011-01-01
A003    2011-01-01

那麼我們如何刪除這些重複的數據呢?

首先,使用以下代碼在該表中插入標識列:

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)  

使用下面的代碼來解決它:

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

另一種可能的方式是

; 

--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #MyTable)
DELETE FROM cte
WHERE  RN > 1;

我使用上面的ORDER BY (SELECT 0) ,因為它是任意的,如果在平局的情況下保留哪一行。

例如,要使用RowID順序保留最新的一個,可以使用ORDER BY RowID DESC

執行計劃

執行計劃通常比接受的答案更簡單,更高效,因為它不需要自加入。

但情況並非總是如此。 GROUP BY解決方案可能更GROUP BY一個地方是散列聚合將優先於流聚合而被選擇的情況。

ROW_NUMBER解決方案將始終提供幾乎相同的計劃,而GROUP BY策略則更加靈活。

可能有利於哈希聚合方法的因素是

  • 在分區列上沒有有用的索引
  • 相對較少的組,每組重複數相對較多

在第二種情況的極端版本中(如果每個組中有很多重複的組),也可以考慮簡單地插入行以保留到新表中,然後對原始數據進行截斷並將它們複製回來以最大限度地減少與刪除相比的日誌記錄這些行的比例非常高。


另一種方法: -

DELETE A
FROM   TABLE A,
       TABLE B
WHERE  A.COL1 = B.COL1
       AND A.COL2 = B.COL2
       AND A.UNIQUEFIELD > B.UNIQUEFIELD 

哦沒問題。 使用臨時表。 如果你想要一個“非常有效”的單一,非常高效的語句,你可以使用:

DELETE FROM MyTable WHERE NOT RowID IN
    (SELECT 
        (SELECT TOP 1 RowID FROM MyTable mt2 
        WHERE mt2.Col1 = mt.Col1 
        AND mt2.Col2 = mt.Col2 
        AND mt2.Col3 = mt.Col3) 
    FROM MyTable mt)

基本上,對於表中的每一行,子選擇會查找與所考慮行完全相同的所有行的頂部RowID。 所以你最終得到一個表示“原始”非重複行的RowID列表。


我寧願CTE從sql server表中刪除重複的行

強烈建議遵循這篇文章:: http://dotnetmob.com/sql-server-article/delete-duplicate-rows-in-sql-server/

保持原創

WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

不保留原創

WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
 
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)

我想我會分享我的解決方案,因為它在特殊情況下工作。 我的情況下,具有重複值的表沒有外鍵(因為這些值是從另一個數據庫中復制的)。

begin transaction
-- create temp table with identical structure as source table
Select * Into #temp From tableName Where 1 = 2

-- insert distinct values into temp
insert into #temp 
select distinct * 
from  tableName

-- delete from source
delete from tableName 

-- insert into source from temp
insert into tableName 
select * 
from #temp

rollback transaction
-- if this works, change rollback to commit and execute again to keep you changes!!

PS:當處理這樣的事情時,我總是使用一個事務,這不僅確保了所有事情都作為一個整體來執行,而且還允許我進行測試而不會冒任何風險。 但是,無論如何,你應該採取備份,以確保...


我會提到這種方法以及它可能會有所幫助,並且適用於所有SQL服務器:通常情況下,只有一個 - 兩個副本,並且已知Ids和重複計數。 在這種情況下:

SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0

我有一張桌子,我需要保留非重複的行。 我不確定速度或效率。

DELETE FROM myTable WHERE RowID IN (
  SELECT MIN(RowID) AS IDNo FROM myTable
  GROUP BY Col1, Col2, Col3
  HAVING COUNT(*) = 2 )

用這個

WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
   As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1

這個查詢對我來說表現非常好:

DELETE tbl
FROM
    MyTable tbl
WHERE
    EXISTS (
        SELECT
            *
        FROM
            MyTable tbl2
        WHERE
            tbl2.SameValue = tbl.SameValue
        AND tbl.IdUniqueValue < tbl2.IdUniqueValue
    )

它從2M的表格(50%重複)中稍微超過30秒中刪除了1M行,



這是另一篇關於刪除重複的好文章。

它討論了為什麼它很難:“ SQL基於關係代數,並且關係代數中不會出現重複項,因為重複項不允許在一個集合中。

臨時表解決方案和兩個mysql示例。

將來你會在數據庫級別還是從應用程序的角度來防止它。 我建議數據庫級別,因為你的數據庫應該負責維護參照完整性,開發人員只會造成問題;)


CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int)

INSERT INTO car(PersonId,CarId)
VALUES(1,2),(1,3),(1,2),(2,4)

--SELECT * FROM car

;WITH CTE as(
SELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car)

DELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn>1)

DELETE
FROM
    table_name T1
WHERE
    rowid > (
        SELECT
            min(rowid)
        FROM
            table_name T2
        WHERE
            T1.column_name = T2.column_name
    );

DELETE LU 
FROM   (SELECT *, 
               Row_number() 
                 OVER ( 
                   partition BY col1, col1, col3 
                   ORDER BY rowid DESC) [Row] 
        FROM   mytable) LU 
WHERE  [row] > 1 

SELECT  DISTINCT *
      INTO tempdb.dbo.tmpTable
FROM myTable

TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable




duplicates