重複レコードの抽出について

質問

以下のようなテーブルからmailが重複していてnameの値が全て同じ値を持つレコードを取得するためのSQL文を教えてください。

mail | name
-----------
aaa | 01
aaa | 01
aaa | 01
bbb | 02
bbb | 02
bbb | 01
ccc | 01
ccc | 01
ccc | 02

select * from test
where mail in
(select mail from test where mail group by mail having count(mail)>1)

上記のSQL文だと以下のデータが取得されてしまいます。
↓
mail | name
-----------
aaa | 01 ○
aaa | 01 ○
aaa | 01 ○
bbb | 02 ×
bbb | 02 ×
ccc | 01 ×
ccc | 01 ×

○印のみ取得したいのですが、
SQL文をどのように修正すればよいのでしょうか。
よろしくお願いいたします。

回答No.3

chukenkenkou

>また、nameの01は値を指定したいです。

どういう意味でしょうか？
検索条件に指定したいという意味ですか？

具体例を説明できますか？

補足

はい。
name='01'を検索条件に入れることは可能でしょうか？

回答No.2

venzou

>mailが重複していて
重複していないものは除外ですよね。

select * from test
where mail in
(
SELECT Q2.mail
FROM
(
SELECT T1.mail, T1.name
FROM test AS T1 INNER JOIN
(
SELECT T0.mail
FROM test AS T0
GROUP BY T0.mail
HAVING count(*) > 1
) AS Q1
ON T1.mail = Q1.mail
GROUP BY T1.mail, T1.name
) AS Q2
GROUP BY Q2.mail
HAVING count(*) = 1
);

長いですが、これで求まりました。（Access2000で確認）

お礼

ご回答ありがとうございました。
参考にさせていただきました！

回答No.1

chukenkenkou

RDBMS名とバージョンは？

重複データを何件も持つ意味があるのですか？

例えば、下記SQLでは、2列とも重複するデータを1回だけ表示します。同じデータを件数分表示する必要があるのですか？

【SQL例】
select mail,max(name) as name
from (select mail,name
from test
group by mail,name) as x
group by x.mail
having count(*)=1;

お礼

参考にさせていただきました。
無事解決しました。
ありがとうございました。

補足

>RDBMS名とバージョンは？
HSQLDBを使用しております。
>重複データを何件も持つ意味があるのですか?
mail | name
-----------
aaa | 01
このような結果がでるようにしたいと思っています。
また、nameの01は値を指定したいです。