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

Question

以前投稿したのですが、抽出するテーブルに問題があり、
再度投稿させて頂きます。
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文で抽出が可能でしょうか？
仮想的にアクセスで検証したのですが、
作成出来ませんでした、すいませんが宜しくお願いします。

jamshid6 · Accepted Answer

>エイリアスを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での重複は絶対に発生しません。
実際の環境に合わせてどのように書き換えられたかわかりませんので、そこをチェックしていただきたいです。

chukenkenkou · Answer

#3回答者です。

jamshid6さんとのやり取りで解決できたとのことで、まずはよかったですね。

質問＆回答の横から入るような形になりましたが、「分析関数」や「WITH句による共通表式」（SQL Serverでは、再帰クエリも可）は非常に便利な機能なので、ぜひ勉強してみてください。

また、#3で示したように、CREATE TABLE文やINSERT文を貼り付けて、アドバイスしようとする側ですぐに利用できるようにしてもらえれば、より多くの人が、より効率的にアドバイスできるので、早く適切なアドバイスが得られる可能性があります。

jamshid6 · Answer

今回のケースで、会員番号が重複する理由はおそらく、質問者さんの会員テーブルの主キーが会員CD、更新日になっていないことによります。
つまり、更新日が最新のものを取るようにしても、１会員１行にならないということです。
（同じ日に同じ会員の情報を２回３回と更新したときにそのテーブルはどうなりますか？）

SELECT * FROM 会員マスタテーブル a1
WHERE a1.KOSHINDAY IN
 (SELECT MAX(KOSHINDAY) FROM 会員マスタテーブル WHERE 会員CD = a1.会員CD)
は、「会員マスタテーブルからそれぞれ更新日が最新のものだけを抽出した結果」を示し、本来この時点で１会員１行になっていなければだめです。
（同じことは代理人テーブルに関しても言えます）

＃３の方が書かれている方法は、「会員単位に更新履歴の降順に並べ替えて先頭の１行を取る」という方法です。
その方法を使えば必ず１会員１行になりますが、同じ更新日で２件以上入っていればどちらが新しいかもわかりませんよね？
更新履歴をすべてマスタが持っているのであれば、必ず最新のデータが特定できるような構造にしておかないといけません（更新順に連番を振るなど）。

その上で、理解していただくために質問にコメントしますが、
＞括弧の後に記述するテーブル名は、どのような役割をするのでしょうか？
括弧の後に記述しているのは、テーブル名ではなく、エイリアス（別名）といいます。
テーブル名の後につけた場合は、そのテーブル名を示しますので、クエリ内で何度もテーブル名を書かなくてよくなります。

今回のように括弧（１つのクエリを括弧で括ったもの）の後につけると、そのクエリの結果に対する別名として使われます。
言いかえると、括弧内のクエリが返す結果をバーチャルなテーブルとみなして使うということです。
（インラインビューと呼んだりします）

また、別名を使うことで、同じテーブルや同じクエリを複数回結合に使うこともできるようになります。aとcは全く同じクエリの結果を結合に使っていますが、別名をつけているので、それぞれ別物として取り扱われます。

chukenkenkou · Answer

まず、苦言から。
ここは、「仕様を提示して、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
;

jamshid6 · Answer

＞会員マスタテーブルの各々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

jamshid6 · Answer

結果をみる限り、業務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

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

>エイリアスをA・B・Cと付けた場合に、次のエラー表示がされるのですが、どのような対処をすれば宜しいのでしょう。

#3回答者です。

今回のケースで、会員番号が重複する理由はおそらく、質問者さんの会員テーブルの主キーが会員CD、更新日になっていないことによります。

この回答への補足

まず、苦言から。

＞会員マスタテーブルの各々KOSHINDAYがMAXデータは、すべて必要です。

この回答への補足

結果をみる限り、業務CDに関わらず代理人がいる会員だけが対象のようですね。

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング