重複していないレコードの抽出方法について

質問

下記のテーブルでフィールドAとBの関係で矛盾が生じているID １と2を抜き出すSQLを必要としております。
AとBを合体させて判断しようと試みましたがダメでした。
ご存知の方ご教示いただけると大変助かります。

【テーブル】
――――――――――
｜ID｜ A ｜B｜
――――――――――
｜1　｜AAA｜B　｜
｜2　｜AAA｜C　｜
｜3　｜BBB｜D　｜
｜4　｜BBB｜D　｜
｜5　｜CCC｜E　｜
｜6　｜DDD｜F　｜
――――――――――
よろしくお願いします。

回答No.5

m3_maki · ベストアンサー

A に対して 複数の B がある場合です。
同じ名前で 住所が異なる場合などを想定しています。

SELECT [テーブル名].*
FROM テーブル名 INNER JOIN
(
SELECT Q.A
FROM
(SELECT [テーブル名].A, [テーブル名].B
FROM テーブル名
GROUP BY [テーブル名].A, [テーブル名].B
) AS Q
GROUP BY Q.A
HAVING Count(Q.A)>1
) AS QQ
ON [テーブル名].A = QQ.A;

お礼

どうもありがとうございます。
自分のレベルでは理解するのが初めは難しいかと思いましたが、分解してみると納得いきました。
ありがとうございました。

回答No.4

chie65536（@chie65535）

Aを基準にBが矛盾している、Bを基準にAが矛盾している、と言う両方の状態を抽出したい場合は、以下の５つのクエリが必要です。

---クエリ1---
SELECT A, Count(A) AS CA1
FROM テーブル1
GROUP BY A;

---クエリ2---
SELECT A, B, Count(A) AS CA2, Count(B) AS CB2
FROM テーブル1
GROUP BY A, B;

---クエリ3---
SELECT B, Count(B) AS CB1
FROM テーブル1
GROUP BY B;

---クエリ4---
SELECT クエリ2.A, クエリ2.B
FROM (クエリ2 LEFT JOIN クエリ1 ON クエリ2.A = クエリ1.A) LEFT JOIN クエリ3 ON クエリ2.B = クエリ3.B
WHERE (((クエリ2.CA2)<>[クエリ1].[CA1])) OR (((クエリ2.CB2)<>[クエリ3].[CB1]));

---結果クエリ---
SELECT テーブル1.ID, テーブル1.A, テーブル1.B
FROM テーブル1 INNER JOIN クエリ4 ON (テーブル1.A=クエリ4.A) AND (テーブル1.B=クエリ4.B)
ORDER BY ID;

ANo.1の回答の応用なので、解説は省略します。

回答No.3

chie65536（@chie65535）

追記。

ANo1の回答では

｜1　｜AAA｜B　｜
｜2　｜AAA｜C　｜
｜3　｜BBB｜D　｜
｜4　｜BBB｜D　｜
｜5　｜CCC｜E　｜
｜6　｜DDD｜F　｜
｜7　｜EEE｜F　｜

と言う状態での「ID6とID7の矛盾」は抽出できません。

「Aを基準にした時に、Bが矛盾してないか？」しか抽出しないので注意して下さい。

回答No.2

chie65536（@chie65535）

解説。

クエリ1では「Aフィールドをグループ化した時の、グループごとのAの件数」を「CA1」として抽出しています。

クエリ2では「Aフィールド、Bフィールドをグループ化した時の、グループごとのAの件数」を「CA2」として抽出しています。

AフィールドとBフィールドに矛盾が無いなら「クエリ1のCA1」と「クエリ2のCA2」は「同じ値」になります。

AフィールドとBフィールドに矛盾があるなら「クエリ1のCA1」と「クエリ2のCA2」は「異なる値」になります。

なので、クエリ3で「クエリ1のCA1とクエリ2のCA2が異なる組み合わせのAとB」を抽出しています。つまり、クエリ3で「矛盾のあるAとB」を抽出しています。

最終的な結果クエリで、矛盾のあるレコードのID、A、Bを抽出しています。

回答No.1

chie65536（@chie65535）

---クエリ1---
SELECT A, Count(A) AS CA1
FROM テーブル1
GROUP BY A;

---クエリ2---
SELECT A, B, Count(A) AS CA2
FROM テーブル1
GROUP BY A, B;

---クエリ3---
SELECT クエリ2.A, クエリ2.B
FROM クエリ2 LEFT JOIN クエリ1 ON クエリ2.A = クエリ1.A
WHERE (((クエリ1.CA1)<>クエリ2.CA2));

---結果クエリ---
SELECT テーブル1.ID, テーブル1.A, テーブル1.B
FROM テーブル1 INNER JOIN クエリ3 ON (テーブル1.B = クエリ3.B) AND (テーブル1.A = クエリ3.A)
ORDER BY ID;

「クエリ１発」では不可能です。

作業用のクエリ１～クエリ３が必要です。

お礼

解説までつけていただいて、また逆のパターンまでいただいて大変感謝です。ありがとうございます。