クエリを分割せずに一つにまとめたクエリについて

質問

以前投稿したのですが、抽出するテーブルに問題があり、
再度投稿させて頂きます。
SQLを使うのが初心者なので勉強をしながら作業を行なっています。そのため説明不足などありましたらご指摘願います。
[環境]
　データベース：SQLServer2005

質問内容は、
会員マスタテーブル
会員CD,会員住所,SHIMEI,KOSHINDAY
0001,東京,太郎,20090101
0001,千葉,太郎,20090102
0002,東京,次郎,20090101
0002,大阪,次郎,20090109
0003,大阪,三郎,20090109
0003,奈良,三郎,20090110
0004,大阪,四郎,20090109

代理人テーブル
会員CD,代理人CD,業務CD,KOSHINDAY
0001,0002,15,20090101
0001,0003,15,20090102
0002,0001,01,20090201
0002,0004,10,20090202
0003,0001,10,20090201
0003,0004,15,20090202
上記のようなテーブルがあります。
抽出結果ですが、
会員CD,会員住所,SHIMEI,KOSHINDAY,代理人CD,代理人住所,代理人名...
0001,千葉,太郎,20090102,0003,奈良,三郎,20090110
0002,大阪,次郎,20090109
0003,奈良,三郎,20090110,0004,大阪,四郎,20090109

会員マスタテーブルにて、各会員のKOSHINDAYが最大のデータを抽出します。
会員マスタテーブルの会員CDを元に、代理人テーブルを検索し各々KOSHINDAYが最大の代理人CDを求め、その時に業務CD＝１５のものだけを選び、
選び出された代理人CDで、会員マスタテーブルを検索し、
代理人の住所・氏名を抽出します。

上記内容なのですが、一回のSQL文で抽出が可能でしょうか？
仮想的にアクセスで検証したのですが、
作成出来ませんでした、すいませんが宜しくお願いします。

回答No.5

jamshid6 · ベストアンサー

>エイリアスをA・B・Cと付けた場合に、次のエラー表示がされるのですが、どのような対処をすれば宜しいのでしょう。
>ちなみにA・B・Cをそれぞれのテーブル名
>A=会員マスタテーブル　B＝代理人テーブルとした場合は、エラーになりません。

クエリの後ろのエイリアスだけ変更して、「ON」の後ろの結合条件を変更しなかったときには、そのようなメッセージが出ます。
たとえば、こんな感じに

LEFT OUTER JOIN
(SELECT * FROM 代理人テーブル b1
WHERE b1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 代理人テーブル WHERE 会員CD=b1.会員CD)) b
ON 代理人テーブル.会員CD=会員マスタテーブル.会員CD

気をつけていただきたいのは、クエリは階層になっているわけですので、最終的なSELECT文はもはやクエリの結果を返すのに
会員マスタテーブルや代理人テーブルを直接は参照しておらず、あくまでも３つのクエリの結果(a,b,c)だけを使っているという点です。
従って、結合にはa,b,cだけを使うことができます。

>会員テーブルの更新日で（MAX関数）による抽出に対しては、１会員１行になる事は、確認済みです。

私が＃２で書いたクエリは、

SELECT * FROM 会員マスタテーブル a1
WHERE a1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 会員マスタテーブル WHERE 会員CD=a1.会員CD)

SELECT * FROM 代理人テーブル b1
WHERE b1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 代理人テーブル WHERE 会員CD=b1.会員CD)

この上記２つのクエリ結果が常に１会員１行になるのであれば、会員CDでの重複は絶対に発生しません。
実際の環境に合わせてどのように書き換えられたかわかりませんので、そこをチェックしていただきたいです。

お礼

やっと出来ました、ありがとうございました。

問題であった重複データが存在した件ですが、
３つのクエリを分割して流した所、
2つ目のクエリで代理人テーブルにてKOSHINDAYが一人の会員に対して重複してました。
理由としては、代理人を登録する際に、業務CDを複数登録出来る為に
KOSHINDAYが同日で業務CDが違うレコードが一人の会員に対して複数存在しました。
その為、他の項目にて重複しないようWHERE文を追加し
3つ目のクエリで聴いている、業務CD = '15' の問い合わせを
2つ目のクエリに追加しました。
こんな感じです。

(SELECT * FROM 代理人テーブル B1
WHERE B1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 代理人テーブル WHERE 会員CD = B1.会員CD)
AND GYOMUCD = '15' AND 終了日 = '999999') B ON B.会員CD = A.会員CD

大変お世話になりました。
又、不明な点がありましたら宜しくお願いします。

回答No.6

chukenkenkou

#3回答者です。

jamshid6さんとのやり取りで解決できたとのことで、まずはよかったですね。

質問＆回答の横から入るような形になりましたが、「分析関数」や「WITH句による共通表式」（SQL Serverでは、再帰クエリも可）は非常に便利な機能なので、ぜひ勉強してみてください。

また、#3で示したように、CREATE TABLE文やINSERT文を貼り付けて、アドバイスしようとする側ですぐに利用できるようにしてもらえれば、より多くの人が、より効率的にアドバイスできるので、早く適切なアドバイスが得られる可能性があります。

回答No.4

jamshid6

今回のケースで、会員番号が重複する理由はおそらく、質問者さんの会員テーブルの主キーが会員CD、更新日になっていないことによります。
つまり、更新日が最新のものを取るようにしても、１会員１行にならないということです。
（同じ日に同じ会員の情報を２回３回と更新したときにそのテーブルはどうなりますか？）

SELECT * FROM 会員マスタテーブル a1
WHERE a1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 会員マスタテーブル WHERE 会員CD = a1.会員CD)
は、「会員マスタテーブルからそれぞれ更新日が最新のものだけを抽出した結果」を示し、本来この時点で１会員１行になっていなければだめです。
（同じことは代理人テーブルに関しても言えます）

＃３の方が書かれている方法は、「会員単位に更新履歴の降順に並べ替えて先頭の１行を取る」という方法です。
その方法を使えば必ず１会員１行になりますが、同じ更新日で２件以上入っていればどちらが新しいかもわかりませんよね？
更新履歴をすべてマスタが持っているのであれば、必ず最新のデータが特定できるような構造にしておかないといけません（更新順に連番を振るなど）。

その上で、理解していただくために質問にコメントしますが、
＞括弧の後に記述するテーブル名は、どのような役割をするのでしょうか？
括弧の後に記述しているのは、テーブル名ではなく、エイリアス（別名）といいます。
テーブル名の後につけた場合は、そのテーブル名を示しますので、クエリ内で何度もテーブル名を書かなくてよくなります。

今回のように括弧（１つのクエリを括弧で括ったもの）の後につけると、そのクエリの結果に対する別名として使われます。
言いかえると、括弧内のクエリが返す結果をバーチャルなテーブルとみなして使うということです。
（インラインビューと呼んだりします）

また、別名を使うことで、同じテーブルや同じクエリを複数回結合に使うこともできるようになります。aとcは全く同じクエリの結果を結合に使っていますが、別名をつけているので、それぞれ別物として取り扱われます。

補足

お忙しい中、回答を頂きありがとうございました。

自分で考えたSQLなどを提示し、不明点を具体的に示すことがルールです。←すいませんでした。以後気を付けます。

先程のご指摘の件ですが、
会員テーブルの更新日で（MAX関数）による抽出に対しては、
１会員１行になる事は、確認済みです。

苦言を言われるかもしれませんが、質問します。
エイリアスをA・B・Cと付けた場合に、次のエラー表示がされるのですが、どのような対処をすれば宜しいのでしょう。
ちなみにA・B・Cをそれぞれのテーブル名
A=会員マスタテーブル　B＝代理人テーブルとした場合は、エラーになりません。

メッセージ 4104、レベル 16、状態 1、行 1
マルチパート識別子 "会員マスタテーブル.会員CD" をバインドできませんでした。

以上です。宜しくお願いします。

回答No.3

chukenkenkou

まず、苦言から。
ここは、「仕様を提示して、SQLなどを作ってもらう」サイトではありません。
自分で考えたSQLなどを提示し、不明点を具体的に示すことがルールです。

SQL Server 2005とのことなので、今回のようなケースで活用できる機能が実装されています。

(1)Oracleでいう「分析関数」
　　特定の列値でグループ化し、最大値あるいは最小値を持つ行の全列を得たいといったことが容易になります。

(2)WITH句による共通表式
　　同じクエリを繰り返し使いたい場合に、ビュー表などを事前に定義したり、同じクエリを何度も書かなくてもよくなります。

１．SQL例
１．１　準備
(1)必要なら表を削除
drop table ktbl;
drop table dtbl;

(2)表の定義
create table ktbl
(kcd char(4),
kaddr varchar(10),
shimei varchar(10),
koshinday char(8));

create table dtbl
(kcd char(4),
dcd char(4),
gcd char(2),
koshinday char(8));

(3)テスト用のデータ
insert into ktbl values('0001','東京','太郎','20090101');
insert into ktbl values('0001','千葉','太郎','20090102');
insert into ktbl values('0002','東京','次郎','20090101');
insert into ktbl values('0002','大阪','次郎','20090109');
insert into ktbl values('0003','大阪','三郎','20090109');
insert into ktbl values('0003','奈良','三郎','20090110');
insert into ktbl values('0004','大阪','四郎','20090109');

insert into dtbl values('0001','0002','15','20090101');
insert into dtbl values('0001','0003','15','20090102');
insert into dtbl values('0002','0001','01','20090201');
insert into dtbl values('0002','0004','10','20090202');
insert into dtbl values('0003','0001','10','20090201');
insert into dtbl values('0003','0004','15','20090202');

２．検索
with
kq(kcd,kaddr,shimei,koshinday)
as
(select
kcd,kaddr,shimei,koshinday
from(select *,row_number() over(partition by kcd order by koshinday desc) as rn
from ktbl) as x
where x.rn=1),
dq(kcd,dcd,gcd,koshinday)
as
(select
kcd,dcd,gcd,koshinday
from(select *,row_number() over(partition by kcd order by koshinday desc) as rn
from dtbl
where gcd='15') as y
where y.rn=1)
select
kq1.kcd as "会員CD",
kq1.kaddr as "会員住所",
kq1.shimei as "会員氏名",
kq1.koshinday as "会員更新日",
kq2.kcd as "代理人CD",
kq2.kaddr as "代理人住所",
kq2.shimei as "代理人氏名",
kq2.koshinday as "代理人更新日"
from kq as kq1
left join dq on kq1.kcd=dq.kcd
left join kq as kq2 on dq.dcd=kq2.kcd
order by kq1.kcd
;

回答No.2

jamshid6

＞会員マスタテーブルの各々KOSHINDAYがMAXデータは、すべて必要です。
普通にやればそうなるので、結果を４行書いておいてくれれば、余計なことは考えなかったんですけどね。
それから、見ての通り、クエリはほとんど違いません。
（つまりほとんどすべてのポイントが含まれていたということです）
前のクエリから導けるようになるといいですね。

SELECT
a.会員CD,
a.会員住所,
a.SHIMEI,
a.KOSHINDAY,
c.会員CD 代理人CD,
c.会員住所 代理人住所,
c.SHIMEI 代理人名,
c.KOSHINDAY
FROM
(SELECT * FROM 会員マスタテーブル a1
WHERE a1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 会員マスタテーブル WHERE 会員CD=a1.会員CD)) a
LEFT OUTER JOIN
(SELECT * FROM 代理人テーブル b1
WHERE b1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 代理人テーブル WHERE 会員CD=b1.会員CD)) b
ON b.会員CD=a.会員CD
LEFT OUTER JOIN
(SELECT * FROM 会員マスタテーブル c1
WHERE c1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 会員マスタテーブル WHERE 会員CD=c1.会員CD)) c
ON c.会員CD=b.代理人CD AND b.業務CD='15'
ORDER BY 1

補足

回答頂いた内容で、処理してみたのですが、
会員ＣＤが重複してしまい抽出件数が膨大になってしまいました。
そこで、確認なのですが、括弧で括られたSELECT文の後に、a b c
とありますが、これは、a = 会員マスタテーブル　b = 代納人テーブルだと思うのですが、
cについては、どうなるのでしょうか？
会員マスタテーブル AS c ということなのでしょうか？

正確な記述をFROM～すると

FROM
(SELECT * FROM 会員マスタテーブル a1
WHERE a1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 会員マスタテーブル WHERE 会員CD = a1.会員CD)) 会員マスタテーブル ←括弧の後に
記述するテーブル名は、
どのような役割をするのでしょうか？
LEFT OUTER JOIN
(SELECT * FROM 代理人テーブル b1
WHERE b1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 代理人テーブル WHERE 会員CD = b1.会員CD)) 代理人テーブル
ON 代理人テーブル.会員CD = 会員マスタテーブル.会員CD
LEFT OUTER JOIN
(SELECT * FROM 会員マスタテーブル c1
WHERE c1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 会員マスタテーブル WHERE 会員CD = c1.会員CD)) c←不明です。
ON 会員マスタテーブル.会員CD = 代理人テーブル.代理人CD AND 代理人テーブル.業務CD = '15'
ORDER BY 会員マスタテーブル.会員CD

以上です。このような記述でよろしいのでしょうか？
お忙しい所、申し訳ございませんがよろしくお願いします。

回答No.1

jamshid6

結果をみる限り、業務CDに関わらず代理人がいる会員だけが対象のようですね。
したがって、メインのテーブルは代理人テーブルとし、以下のようにつなぎます。

SELECT
a.会員CD,
b.会員住所,
b.SHIMEI,
b.KOSHINDAY,
c.会員CD 代理人CD,
c.会員住所 代理人住所,
c.SHIMEI 代理人名,
c.KOSHINDAY
FROM
(SELECT * FROM 代理人テーブル a1
WHERE a1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 代理人テーブル WHERE 会員CD=a1.会員CD)) a
LEFT OUTER JOIN
(SELECT * FROM 会員マスタテーブル b1
WHERE b1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 会員マスタテーブル WHERE 会員CD=b1.会員CD)) b
ON b.会員CD=a.会員CD
LEFT OUTER JOIN
(SELECT * FROM 会員マスタテーブル c1
WHERE c1.KOSHINDAY IN
(SELECT MAX(KOSHINDAY) FROM 会員マスタテーブル WHERE 会員CD=c1.会員CD)) c
ON c.会員CD=a.代理人CD AND a.業務CD='15'
ORDER BY 1

補足

素早い解答ありがとうございます。
せっかくの解答なのでが、忘れていました。
会員マスタテーブルの各々KOSHINDAYがMAXデータは、すべて必要です。
その中には、代理人テーブルに会員CDを持たない人も存在しますが
代理人が居ない会員は、代理人の住所等は、NULLでOKなのです。
すいませんが宜しくお願いします。

こんな感じです。
会員CD,会員住所,SHIMEI,KOSHINDAY,代理人CD,代理人住所,代理人名...
0001,千葉,太郎,20090102,0003,奈良,三郎,20090110
0002,大阪,次郎,20090109
0003,奈良,三郎,20090110,0004,大阪,四郎,20090109
0004,大阪,四郎,20090109