accessを自在に操りたい。
現状クエリにクエリを重ねて重ねて重ねてなんとか出したいものを出している。
SQLを自在に書ければするっと一発でできるに違いない。
なので、自宅待機中の機会にSQLを勉強中。の自分用記録。
◆やりたいこと：クエリ「Q_フラグ１抽出」にある取引店ID,取引先IDに合致しないレコードをクエリ「Q_EB」から抽出したい。
赤線で消したレコード以外のレコードが抽出されるはず
以下 ★は説明用印。SQLとは関係なし
①外部結合で実現
SELECT Q.取引店ID, Q.取引先ID, Q1.取引店ID, Q1.取引先ID ★1
FROM Q_EB AS Q LEFT JOIN Q_フラグ1抽出 AS Q1 ON (Q.取引店ID = Q1.取引店ID) AND (Q.取引先ID = Q1.取引先ID) ★2
WHERE Q1.取引店ID IS NULL; ★3
★1
Q_EBから取引店IDと取引先IDを出力
Q_フラグ1抽出から取引店IDと取引先IDを出力（確認用）
★2
Q_EBをQと置き換え
Q_フラグ1抽出をQ1と置き換え
左外部結合でクエリを連結（Q_EBからすべてのレコード、Q_フラグ1抽出はNullになるレコードがある）
★3
Q_フラグ1抽出がNullになるレコードを出力
さらに
グループ化して取引店IDと取引先IDの重複削除（★4）
確認用のQ_フラグ1抽出から取引店IDと取引先IDは表示しない
SELECT Q.取引店ID, Q.取引先ID
FROM Q_EB AS Q LEFT JOIN Q_フラグ1抽出 AS Q1 ON (Q.取引店ID = Q1.取引店ID) AND (Q.取引先ID = Q1.取引先ID)
WHERE Q1.取引店ID IS NULL
GROUP BY Q.取引店ID, Q.取引先ID; ★４
②サブクエリで実現
SELECT 取引店ID, 取引先ID
FROM Q_EB
WHERE 取引店ID & 取引先ID IN (SELECT 取引店ID & 取引先ID FROM Q_フラグ1抽出); ★5
★5：サブクエリが返す結果は1フィールドだけ、らしいので
取引店ID と 取引先ID を連結して1フィールドした。
IN で一致するレコードを抽出。
なので、不一致レコード抽出は NOT IN に変更。（★6）
SELECT 取引店ID, 取引先ID
FROM Q_EB
WHERE 取引店ID & 取引先ID NOT IN (SELECT 取引店ID & 取引先ID FROM Q_フラグ1抽出); ★6
さらに
グループ化して取引店IDと取引先IDの重複削除（★7）
SELECT 取引店ID, 取引先ID
FROM Q_EB
WHERE 取引店ID & 取引先ID NOT IN (SELECT 取引店ID & 取引先ID FROM Q_フラグ1抽出)
GROUP BY 取引店ID, 取引先ID; ★７
以下SQLでQ_フラグ1抽出クエリを作成している
SELECT Q.取引店ID, Q.取引先ID
FROM Q_EB AS Q
WHERE (((IIf([Q].[Eフラグ]=1,1,IIf([Q].[Sフラグ]=1,1,0)))=1))
GROUP BY Q.取引店ID, Q.取引先ID
HAVING SUM(Q.件数)>0;
これも②のサブクエリに入れ込めるのではないか。→③
SELECT 取引店ID, 取引先ID
FROM Q_EB
WHERE 取引店ID & 取引先ID
NOT IN
(SELECT
取引店ID & 取引先ID
FROM Q_EB
WHERE (((IIf(Eフラグ=1,1,IIf(Sフラグ=1,1,0)))=1))
GROUP BY 取引店ID, 取引先ID
HAVING SUM(件数)>0)
GROUP BY 取引店ID, 取引先ID;
できたっぽい。
しかしわかりにくい。
①②③とも下図の結果になりました。
2020.4.26.追記
②③の方法はaccessでは効率が悪いことに気が付きました。
会社でそこそこでかいデータでやってみたら5分待ってもしーんとしてて、どうやら時間がかかるらしい。サブクエリってなんかできる人っぽくって使ってみたかったけど①の方法に切り替えました。
以下④の NOT EXISTS という方法でも同じ結果が得られました。
知らなかったー。勉強になったわー。
④
SELECT 取引店ID, 取引先ID
FROM Q_EB AS Q1
WHERE NOT exists
(SELECT * FROM Q_フラグ1抽出 AS Q2
WHERE Q1.取引店ID & Q1.取引先ID = Q2.取引店ID & Q2.取引先ID)
GROUP BY 取引店ID, 取引先ID;
こちらの記事にてNOT IN NOT EXISTS の速度等勉強させていただきました。
https://blog.withonoware.co.jp/tech/2017/09/24/
『not-in』『not-exists』の検証/
コメント