English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

  1. 現在の位置
  2. ホームページ
  3. 経験ノート
  4. 本文

SQLでのグループ化後の二次集計（テーブルの重複レコードの検索と削除の処理）の実現方法

--テーブルの重複記録処理(クエリと削除)
/******************************************************************************************************************************************************
1、Num、Nameが同じ重複値記録、大小関係がない場合1件のみを保持
2、Nameが同じでIDが大小関係がある場合、大きいまたは小さい方の1つの記録を保持
整理人：中国風(Roy)

日付:2008.06.06
******************************************************************************************************************************************************/

--1、重複処理記録のクエリに使用(列が大小関係を持っていない場合2000用生成自增列和临时表处理,SQL2005用row_number関数処理)

--> --> (Roy)テストデータ生成
　

if not object_id('Tempdb..#T') is null
 テーブル#Tを削除
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1'),[Memo] nvarchar(
select 2,N'A',N'A2'),[Memo] nvarchar(
select 3,N'A',N'A3'),[Memo] nvarchar(
select 4,N'B',N'B1'),[Memo] nvarchar(
select 5,N'B',N'B2'
Go


--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05時、効率が高い1、2
メソッド1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)

メソッド2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID

メソッド3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)

メソッド4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1

メソッド5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)

メソッド6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0

メソッド7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)

メソッド8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)

メソッド9(注:IDがユニークである場合のみ使用可能):
select * from #T a where ID in(select min(ID) from #T group by Name)

--SQL2005:

メソッド10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

メソッド11:

select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1

結果生成:
/*
ID　　Name Memo
----------- ---- ----
1　　　　　　　　　　 A　　A1
4　　　　　　　　　　 B　　B1

(2 行が影響を受ける)
*/


--II、Name相同ID最大的記録,与min相反:
メソッド1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)

メソッド2:
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID

メソッド3:
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID

メソッド4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1

メソッド5:
select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)

メソッド6:
select * from #T a where (select count(1from #T where Name=a.Name and ID>a.ID)=0

メソッド7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)

メソッド8:
select * from #T a where ID!<all(select ID from #T where Name=a.Name)

メソッド9(注:IDがユニークである場合のみ使用可能):
select * from #T a where ID in(select max(ID) from #T group by Name)

--SQL2005:

メソッド10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

メソッド11:
select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1

生成结果2:
/*
ID　　Name Memo
----------- ---- ----
3　　　　　　　　　　 A　　A3
5　　　　　　　　　　 B　　B2

(2 行が影響を受ける)
*/

--2、删除重复记录有大小关系时,保留大或小其中一个记录


--> --> (Roy)テストデータ生成

if not object_id('Tempdb..#T') is null
　　　 テーブル#Tを削除
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1'),[Memo] nvarchar(
select 2,N'A',N'A2'),[Memo] nvarchar(
select 3,N'A',N'A3'),[Memo] nvarchar(
select 4,N'B',N'B1'),[Memo] nvarchar(
select 5,N'B',N'B2'
Go

--I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
メソッド1:
delete a from #T a where　 exists(select 1 from #T where Name=a.Name and ID<a.ID)

メソッド2:
delete a　from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

メソッド3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)

メソッド4(注:IDがユニークである場合のみ使用可能):
delete a from #T a where ID not in(select min(ID)from #T group by Name)

メソッド5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0

メソッド6:
delete a from #T a where ID>(select top 1 ID from #T where Name=a.name order by ID)

メソッド7:
delete a from #T a where ID>any(select ID from #T where Name=a.Name)

select * from #T

結果生成:
/*
ID　　Name Memo
----------- ---- ----
1　　　　　　　　　　 A　　A1
4　　　　　　　　　　 B　　B1

(2 行が影響を受ける)
*/


--II、Nameが同じ場合、IDの最大値のレコードを保持:

メソッド1:
delete a from #T a where　 exists(select 1 from #T where Name=a.Name and ID>a.ID)

メソッド2:
delete a　 from #T a left join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

メソッド3:
delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name)

メソッド4(注:IDがユニークである場合のみ使用可能):
delete a from #T a where ID not in(select max(ID)from #T group by Name)

メソッド5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)>0

メソッド6:
delete a from #T a where ID>(select top 1 ID from #T where Name=a.name order by ID desc)

メソッド7:
delete a from #T a where ID<any(select ID from #T where Name=a.Name)


select * from #T
/*
ID　　Name Memo
----------- ---- ----
3　　　　　　　　　　 A　　A3
5　　　　　　　　　　 B　　B2

(2 行が影響を受ける)
*/

--3、重複記録が大小関係がない場合、重複値の処理


--> --> (Roy)テストデータ生成
　
if not object_id('Tempdb..#T') is null
　　　 テーブル#Tを削除
Go
Create table #T([Num] int,[Name] nvarchar(1))
Insert #T
select 1,N'A' union all
select 1,N'A' union all
select 1,N'A' union all
select 2,N'B' union all
select 2,N'B'
Go

メソッド1:
if object_id('Tempdb..#') is not null
　　　 テーブル#を削除
Select distinct * into # from #T--重複記録を排除した結果セットを一時テーブル生成#

table #Tをtruncate--table #Tをクリア

insert #T select * from #　　　 --一時表#をtable #Tに挿入

--結果を確認
select * from #T

/*
Num　　　　　　　　 Name
----------- ----
1　　　　　　　　　　 A
2　　　　　　　　　　 B

(2 行が影響を受ける)
*/

--テストデータを再実行後、メソッドを使用2
メソッド2:

table #TにID int identityを追加--識別列を追加
go
delete a from　 #T a where　 exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--1つのレコードのみを保持
go
table #Tからcolumn IDを削除--識別列を削除

--結果を確認
select * from #T

/*
Num　　　　　　　　 Name
----------- ----
1　　　　　　　　　　 A
2　　　　　　　　　　 B

(2 行が影響を受ける)

*/

--テストデータを再実行後、メソッドを使用3
メソッド3:
declare Roy_Cursor cursor local for
select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1
declare @con int,@Num int,@Name nvarchar(1)
Roy_Cursorを開く
Roy_Cursorから次のレコードをfetchし、@con、@Num、@Nameに格納
while @@Fetch_status=0
begin
　　　 rowcountを@conにセット;
　　　 #TでNum=@NumおよびName=@Nameのレコードを削除
　　　 rowcountを0にセット;
　　　 Roy_Cursorから次のレコードをfetchし、@con、@Num、@Nameに格納
end
Roy_Cursorを閉じる
Roy_Cursorを解除

--結果を確認
select * from #T
/*
Num　　　　　　　　 Name
----------- ----
1　　　　　　　　　　 A
2　　　　　　　　　　 B

(2 行が影響を受ける)

基本教程
おすすめ