[SQLServer]複数カラムの重複レコード抽出

重複レコードのみ抽出

No	性別	地区
1	man	Tokyo
2	man	Osaka
3	man	Tokyo
4	woman	Tokyo
5	woman	Osaka

というテーブルがあったとして
性別と地区が重複しているレコード(No1,3)のみ抜き出したい場合のSQLは

GroupByquery

select 性別 ,地区 from testTBL
group by 性別 ,地区
having count(性別) >1 and count(地区)>1

性別	地区
man	Tokyo
man	Tokyo
となる

重複レコードの全カラム表示

Noも知りたいのでサブクエリにして実行する

Complete(MySQL)

select * from testTBL
where (性別 ,地区) in (
    select 性別 ,地区 from testTBL
    group by 性別 ,地区
    having count(性別) >1 and count(地区)>1 
)
メッセージ 4145、レベル 15、状態 1、行 28
条件が必要なコンテキストに対し、',' 付近でブール型以外の式が指定されました。

MySQLではできるのに...
しょうがないのでEXISTSを使用する

Miss

select * from testTBL
where EXISTS (
    select 性別 ,地区 from testTBL
    group by 性別 ,地区
    having count(性別) >1 and count(地区)>1
 )

No	性別	地区
1	man	Tokyo
2	man	Osaka
3	man	Tokyo
4	woman	Tokyo
5	woman	Osaka
は？
調べたらテーブル同士の紐づきがないため、
該当レコードを持つtestTBLの全レコードが表示されてしまっていた

Complete

select * from testTBL AS a
where EXISTS (
    select 性別 ,地区 from testTBL AS b
    a.性別 = b.性別
    a.地区 = b.地区
    group by 性別 ,地区
    having count(性別) >1 and count(地区)>1
 )

No	性別	地区
1	man	Tokyo
3	man	Tokyo

意地でもEXISTSもJOINもしたくない人向け

Complete(Gorioshi)

select * from testTBL 
where 性別 in (
    select 性別 from testTBL 
    group by 性別 ,地区
    having count(性別) >1 and count(地区)>1
)
and 地区 in (
    select 地区 from testTBL 
    group by 性別 ,地区
    having count(性別) >1 and count(地区)>1
)

文字列同士なら結合して一つのカラムとして扱うほうが楽かも

参考

・MySQL 複数のコラムをGROUP BYして条件にしたレコードを取得したい
・TECHSCORE：SQL 7章　サブクエリ