SQL Server에서 중복된 행을 삭제하는 방법은 무엇입니까?
중복 행을 삭제하려면 어떻게 해야 합니까?unique row 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)
)
와 CTE를 .ROW_NUMBER두 개의 결합을 통해 어떤 행이 삭제(또는 업데이트)되는지 확인할 수 있으므로, 그냥 변경합니다.DELETE FROM CTE...SELECT * FROM CTE:
WITH CTE AS(
SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1
데모 (결과가 다릅니다. 귀하의 오타 때문인 것 같습니다.)
COL1 COL2 COL3 COL4 COL5 COL6 COL7
john 1 1 1 1 1 1
sally 2 2 2 2 2 2
는 중복 항목을 로 결정합니다.col1에 때문에PARTITION BY col1을 러개 열열면을에다니에 .PARTITION BY:
ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)
sql server 테이블에서 중복된 행을 삭제하기 위해 CTE를 선호합니다.
이 기사를 따르는 것을 강력히 추천합니다:: http://codaffection.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)
을 사용하지 CTE그리고.ROW_NUMBER()그룹바이를 사용하여 레코드를 삭제할 수 있습니다.MAX에 함수가 되어 있습니다.
DELETE
FROM MyDuplicateTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyDuplicateTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
외부 키와 같이 참조가 없는 경우 이 작업을 수행할 수 있습니다.개념 증명을 테스트할 때나 테스트 데이터가 중복될 때 많이 합니다.
SELECT DISTINCT [col1],[col2],[col3],[col4],[col5],[col6],[col7]
INTO [newTable]
FROM [oldTable]
개체 탐색기로 이동하여 이전 테이블을 삭제합니다.
새 테이블의 이름을 이전 테이블의 이름으로 바꿉니다.
모든 중복 항목을 제거하지만 첫 번째 항목(최소 ID)만 제거합니다.
Postgres와 같은 다른 SQL 서버에서도 동일하게 작동해야 합니다.
DELETE FROM table
WHERE id NOT IN (
select min(id) from table
group by col1, col2, col3, col4, col5, col6, col7
)
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
)
두 가지 솔루션이 있습니다.mysql:
문을 사용하여 중복 행 삭제
DELETE t1 FROM contacts t1
INNER JOIN contacts t2
WHERE
t1.id < t2.id AND
t1.email = t2.email;
을 두 번 인 " 이쿼는 연테을두테로별사다용니합칭을이블므하조참번리이락블처▁table▁alias▁the▁this▁refe다▁twice니합용"를 사용합니다.t1그리고.t2.
출력은 다음과 같습니다.
1 쿼리 정상, 4열 영향(0.10초)
된 행을 행을 lowest id다음 문장을 사용할 수 있습니다.
DELETE c1 FROM contacts c1
INNER JOIN contacts c2
WHERE
c1.id > c2.id AND
c1.email = c2.email;
중간 테이블을 사용하여 중복 행 삭제
다음은 중간 테이블을 사용하여 중복 행을 제거하는 단계를 보여 줍니다.
중복 행을 삭제할 원래 테이블과 동일한 구조로 새 테이블을 만듭니다.
원래 테이블의 개별 행을 바로 옆 테이블에 삽입합니다.
원래 테이블의 개별 행을 바로 옆 테이블에 삽입합니다.
1단계. 원래 테이블과 구조가 동일한 새 테이블을 만듭니다.
CREATE TABLE source_copy LIKE source;
2단계. 원래 테이블에서 새 테이블로 개별 행을 삽입합니다.
INSERT INTO source_copy
SELECT * FROM source
GROUP BY col; -- column that has duplicate values
3단계. 원래 테이블을 삭제하고 직접 테이블의 이름을 원래 테이블로 변경합니다.
DROP TABLE source;
ALTER TABLE source_copy RENAME TO source;
출처: http://www.mysqltutorial.org/mysql-delete-duplicate-rows/
아래의 삭제 방법도 참조하시기 바랍니다.
Declare @table table
(col1 varchar(10),col2 int,col3 int, col4 int, col5 int, col6 int, col7 int)
Insert into @table values
('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)
이름이 지정된 샘플 테이블을 만들었습니다.@table주어진 데이터로 로드했습니다.
Delete aliasName from (
Select *,
ROW_NUMBER() over (Partition by col1,col2,col3,col4,col5,col6,col7 order by col1) as rowNumber
From @table) aliasName
Where rowNumber > 1
Select * from @table
을 줄 : 의모열제을공는경우하든고참▁in▁the▁note우는Partition by에 분부, 그다음order by큰 의미가 없습니다.
알아요, 그 질문은 3년 전에 한 질문이고, 제 대답은 팀이 올린 글의 다른 버전입니다. 하지만 만약을 위해 그것을 게시하는 것은 누구에게나 도움이 됩니다.
SQL 서버에서 여러 가지 방법으로 수행할 수 있는 가장 간단한 방법은 다음과 같습니다.중복 행 테이블에서 새 임시 테이블로 고유 행을 삽입합니다.그런 다음 중복 행 테이블에서 모든 데이터를 삭제한 다음 아래와 같이 중복이 없는 임시 테이블에서 모든 데이터를 삽입합니다.
select distinct * into #tmp From table
delete from table
insert into table
select * from #tmp drop table #tmp
select * from table
CTE(Common Table Expression)를 사용하여 중복 행 삭제
With CTE_Duplicates as
(select id,name , row_number()
over(partition by id,name order by id,name ) rownumber from table )
delete from CTE_Duplicates where rownumber!=1
SQL 서버의 테이블에서 중복 행을 삭제하려면 다음 단계를 수행합니다.
- GROUP BY 절 또는 ROW_NUMBER() 함수를 사용하여 중복된 행을 찾습니다.
- 중복 행을 제거하려면 DELETE 문을 사용합니다.
샘플 테이블 설정
DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts(
contact_id INT IDENTITY(1,1) PRIMARY KEY,
first_name NVARCHAR(100) NOT NULL,
last_name NVARCHAR(100) NOT NULL,
email NVARCHAR(255) NOT NULL,
);
값 삽입
INSERT INTO contacts
(first_name,last_name,email)
VALUES
('Syed','Abbas','syed.abbas@example.com'),
('Catherine','Abel','catherine.abel@example.com'),
('Kim','Abercrombie','kim.abercrombie@example.com'),
('Kim','Abercrombie','kim.abercrombie@example.com'),
('Kim','Abercrombie','kim.abercrombie@example.com'),
('Hazem','Abolrous','hazem.abolrous@example.com'),
('Hazem','Abolrous','hazem.abolrous@example.com'),
('Humberto','Acevedo','humberto.acevedo@example.com'),
('Humberto','Acevedo','humberto.acevedo@example.com'),
('Pilar','Ackerman','pilar.ackerman@example.com');
쿼리
SELECT
contact_id,
first_name,
last_name,
email
FROM
contacts;
테이블에서 중복 행 삭제
WITH cte AS (
SELECT
contact_id,
first_name,
last_name,
email,
ROW_NUMBER() OVER (
PARTITION BY
first_name,
last_name,
email
ORDER BY
first_name,
last_name,
email
) row_num
FROM
contacts
)
DELETE FROM cte
WHERE row_num > 1;
지금 레코드를 삭제해야 합니다.
사용 시도:
SELECT linkorder
,Row_Number() OVER (
PARTITION BY linkorder ORDER BY linkorder DESC
) AS RowNum
FROM u_links
Microsoft는 중복 제거 방법에 대한 매우 깔끔한 가이드를 제공합니다.http://support.microsoft.com/kb/139444 를 확인해 보세요.
간단히 말해, 삭제할 행이 몇 개 없을 때 중복을 삭제하는 가장 쉬운 방법은 다음과 같습니다.
SET rowcount 1;
DELETE FROM t1 WHERE myprimarykey=1;
기본 키는 행의 식별자입니다.
나는 설정rowcount중복된 행이 두 개밖에 없었기 때문에 1.만약 내가 3개의 행을 중복했다면, 나는 행 수를 2로 설정했을 것입니다. 그러면 행 수는 처음에 본 두 개는 삭제되고 테이블 t1에는 한 개만 남습니다.
with myCTE
as
(
select productName,ROW_NUMBER() over(PARTITION BY productName order by slno) as Duplicate from productDetails
)
Delete from myCTE where Duplicate>1
위에서 제안한 솔루션을 사용해 본 결과, 소규모 중간 테이블에 적용됩니다.저는 그 해결책을 매우 큰 테이블에 제안할 수 있습니다.반복적으로 실행되기 때문입니다.
- 의모 종속보 삭합다니의 합니다.
LargeSourceTable - sql management studio를 사용하여 종속성을 찾을 수 있습니다. 테이블을 마우스 오른쪽 버튼으로 클릭하고 "의존성 보기"를 클릭합니다.
- 테이블 이름 변경:
sp_rename 'LargeSourceTable', 'LargeSourceTable_Temp'; GO- 다음을 생성합니다.
LargeSourceTable다시 말하지만, 이제 중복 추가를 정의하는 모든 열이 포함된 기본 키를 추가합니다.WITH (IGNORE_DUP_KEY = ON) 예:
CREATE TABLE [dbo].[LargeSourceTable] ( ID int IDENTITY(1,1), [CreateDate] DATETIME CONSTRAINT [DF_LargeSourceTable_CreateDate] DEFAULT (getdate()) NOT NULL, [Column1] CHAR (36) NOT NULL, [Column2] NVARCHAR (100) NOT NULL, [Column3] CHAR (36) NOT NULL, PRIMARY KEY (Column1, Column2) WITH (IGNORE_DUP_KEY = ON) ); GO새로 만든 테이블에 대해 처음에 놓았던 보기 다시 만들기
이제 다음 SQL 스크립트를 실행하면 페이지당 1,000,000 행으로 결과가 표시됩니다. 페이지당 행 번호를 변경하여 결과를 더 자주 볼 수 있습니다.
참고로, 내가 설정한 것은
IDENTITY_INSERT열 중 하나에 자동 증분 ID가 포함되어 있기 때문에 켜지거나 꺼집니다. 이 ID도 복사하고 있습니다.
SET IDENTITY_INSERT LargeSourceTable ON DECLARE @PageNumber AS INT, @RowspPage AS INT DECLARE @TotalRows AS INT declare @dt varchar(19) SET @PageNumber = 0 SET @RowspPage = 1000000 select @TotalRows = count (*) from LargeSourceTable_TEMP
While ((@PageNumber - 1) * @RowspPage < @TotalRows )
Begin
begin transaction tran_inner
; with cte as
(
SELECT * FROM LargeSourceTable_TEMP ORDER BY ID
OFFSET ((@PageNumber) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY
)
INSERT INTO LargeSourceTable
(
ID
,[CreateDate]
,[Column1]
,[Column2]
,[Column3]
)
select
ID
,[CreateDate]
,[Column1]
,[Column2]
,[Column3]
from cte
commit transaction tran_inner
PRINT 'Page: ' + convert(varchar(10), @PageNumber)
PRINT 'Transfered: ' + convert(varchar(20), @PageNumber * @RowspPage)
PRINT 'Of: ' + convert(varchar(20), @TotalRows)
SELECT @dt = convert(varchar(19), getdate(), 121)
RAISERROR('Inserted on: %s', 0, 1, @dt) WITH NOWAIT
SET @PageNumber = @PageNumber + 1
End
SET IDENTITY_INSERT LargeSourceTable OFF
-- 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
중복된 레코드를 필드에 따라 그룹화한 다음 레코드 중 하나를 보유하고 나머지를 삭제해야 합니다.예:
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)
대용량(수백만 개의 레코드) 테이블에서 중복 항목을 삭제하는 데 시간이 오래 걸릴 수 있습니다.삭제하는 대신 선택한 행의 임시 테이블에 대량 삽입하는 것이 좋습니다.
--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;
이것은 당신의 경우에 도움이 될 수 있습니다.
DELETE t1 FROM table t1 INNER JOIN table t2 WHERE t1.id > t2.id AND t1.col1 = t2.col1
중복을 제거하는 아이디어는 다음과 같습니다.
- 중복되지 않는 행 보호
- 함께 한정된 여러 행 중 하나를 중복으로 유지합니다.
단계별
- 먼저 중복의 정의를 충족하는 행을 식별하고 #tableAll과 같이 임시 테이블에 삽입합니다.
- 중복되지 않는(단일 행) 행 또는 temp table에 대한 고유 행을 선택합니다(#tableUnique).
- 원본 테이블에서 삭제 #tableAll에 가입하여 중복 항목을 삭제합니다.
- #tableUnique의 모든 행을 원본 테이블에 삽입합니다.
- 드롭 #tableAll 및 #tableUnique
테이블에 일시적으로 열을 추가할 수 있는 기능이 있다면, 이것이 저에게 효과적인 해결책이었습니다.
ALTER TABLE dbo.DUPPEDTABLE ADD RowID INT NOT NULL IDENTITY(1,1)
그런 다음 MIN과 GROUP BY의 조합을 사용하여 DELETE를 수행합니다.
DELETE b
FROM dbo.DUPPEDTABLE b
WHERE b.RowID NOT IN (
SELECT MIN(RowID) AS RowID
FROM dbo.DUPPEDTABLE a WITH (NOLOCK)
GROUP BY a.ITEM_NUMBER,
a.CHARACTERISTIC,
a.INTVALUE,
a.FLOATVALUE,
a.STRINGVALUE
);
DELETE가 올바르게 수행되었는지 확인합니다.
SELECT a.ITEM_NUMBER,
a.CHARACTERISTIC,
a.INTVALUE,
a.FLOATVALUE,
a.STRINGVALUE, COUNT(*)--MIN(RowID) AS RowID
FROM dbo.DUPPEDTABLE a WITH (NOLOCK)
GROUP BY a.ITEM_NUMBER,
a.CHARACTERISTIC,
a.INTVALUE,
a.FLOATVALUE,
a.STRINGVALUE
ORDER BY COUNT(*) DESC
결과에는 카운트가 1보다 큰 행이 없어야 합니다.마지막으로 rowid 열을 제거합니다.
ALTER TABLE dbo.DUPPEDTABLE DROP COLUMN RowID;
오와, 나는 이 모든 대답을 준비해서 너무 바보 같은 기분이 듭니다, 그것들은 모든 CTE와 임시 테이블 등과 함께 전문가들의 대답과 같습니다.
그리고 제가 그것을 작동시키기 위해 한 일은 단지 MAX를 사용하여 ID 열을 집계하는 것이었습니다.
DELETE FROM table WHERE col1 IN (
SELECT MAX(id) FROM table GROUP BY id HAVING ( COUNT(col1) > 1 )
)
참고: 중복을 제거하려면 여러 번 실행해야 할 수 있습니다. 중복 행 집합이 한 번에 하나씩만 삭제되기 때문입니다.
SELECT 명령 바로 뒤에 DISTINCT 키워드를 추가하기만 하면 됩니다. 예:
SELECT DISTICNT ColumnOne, ColumnTwo, ColumnThree
FROM YourTable
한 번에 정보를 잃지 않고 게시된 행을 제거하는 또 다른 방법은 다음과 같습니다.
delete from dublicated_table t1 (nolock)
join (
select t2.dublicated_field
, min(len(t2.field_kept)) as min_field_kept
from dublicated_table t2 (nolock)
group by t2.dublicated_field having COUNT(*)>1
) t3
on t1.dublicated_field=t3.dublicated_field
and len(t1.field_kept)=t3.min_field_kept
DECLARE @TB TABLE(NAME VARCHAR(100));
INSERT INTO @TB VALUES ('Red'),('Red'),('Green'),('Blue'),('White'),('White')
--**Delete by Rank**
;WITH CTE AS(SELECT NAME,DENSE_RANK() OVER (PARTITION BY NAME ORDER BY NEWID()) ID FROM @TB)
DELETE FROM CTE WHERE ID>1
SELECT NAME FROM @TB;
--**Delete by Row Number**
;WITH CTE AS(SELECT NAME,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) ID FROM @TB)
DELETE FROM CTE WHERE ID>1;
SELECT NAME FROM @TB;
DELETE FROM TBL1 WHERE ID IN
(SELECT ID FROM TBL1 a WHERE ID!=
(select MAX(ID) from TBL1 where DUPVAL=a.DUPVAL
group by DUPVAL
having count(DUPVAL)>1))
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
언급URL : https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server
'programing' 카테고리의 다른 글
| SET READ_ 기간COMMITED_SNAPHOT 테이크? (0) | 2023.05.07 |
|---|---|
| Kestrel이란 무엇입니까(vs IIS / Express) (0) | 2023.05.07 |
| WPF 연결 속성 데이터 바인딩 (0) | 2023.05.07 |
| 페이지 앵커에 해시태그를 사용한 Angular2 라우팅 (0) | 2023.05.07 |
| 분기의 내용을 새 로컬 분기로 복사하려면 어떻게 해야 합니까? (0) | 2023.05.07 |