テーブルの結合(GROUP BY句の制約について)

質問

次のような操作がしたいのですが、
Test1
t1
1
2

Test2
tt1 tt2 tt3
1　　1　　aa
1　　2　　bb -ココ
2　　1　　cc
2　　2　　dd
2　　3　　ee -ココ

結果
Test1とTest2を関連付けて
t1 tt1 tt2 tt3
1　1　2 aa
1　2　3 ee
Test2.tt2をマックス関数での参照です。

一応次のような操作をしてみました。
select T1, X.tt1, X.tt2, X.tt3
from Test1
inner join (select tt1, max(tt2) as tt2, tt3 from Test2 group by tt1, tt3) as X
on (t1 = X.tt1)

group by句にtt1とtt3を付けいるせいか、
すべて(Test1とTest2)が表示されます。
groupBY句の制約のせいですが・・・

何かアドバイス等がありましたら、宜しく御願いします。

回答No.1

chukenkenkou · ベストアンサー

結果の2行目は、以下の誤りですよね？

t1 tt1 tt2 tt3
2 2 3 ee

幾つかのステップを踏んで、SQLを組んだ方がいいでしょう。

【step1】
Test2表のtt1列でグループ化し、tt1列の値毎のtt2列の最大値を得る

select tt1,max(tt2) as tt2 from Test2 group by tt1

※単純なselect文では、同時にtt3列の値は得られない

【step2】
step1の問い合わせで得たtt1列、tt2列（tt1列の値毎の最大値）から、対応するtt3列の値を得る

select y.tt1,y.tt2,y.tt3
from Test2 as y
where (tt1,tt2) in(select tt1,max(tt2) as tt2 from Test2 group by tt1)

と書ければいいのですが、SQL Serverは、「(列1,列2) in(select ～)」という書き方ができないようなのです。
そこで、existsかjoinにしますが、ここではjoinの例にします。

select y.tt1,y.tt2,y.tt3
from Test2 as y
inner join (select tt1,max(tt2) as tt2 from Test2 group by tt1) as z
on y.tt1=z.tt1 and y.tt2=z.tt2;

【step3】
t1列とtt1列でTest1表とTest2表を結合します。

select t1,tt1,tt2,tt3
from Test1
inner join
(select y.tt1 as tt1,y.tt2 as tt2,y.tt3 as tt3
from Test2 as y
inner join (select tt1,max(tt2) as tt2 from Test2 group by tt1) as z
on y.tt1=z.tt1 and y.tt2=z.tt2) as x
on t1=x.tt1
order by t1,tt1;

お礼

ご丁寧な解説。誠にありがとうございます。
頭が下がります。

>>※単純なselect文では、同時にtt3列の値は得られない
素人がよく陥る場所だということが、chukenkenkou様には
分かっているようですね。

汚いソースだとは思いますが、自分なりもう一度
考えてみたので。晒します。
select t1.t1, X.tt1, X.tt2, Y.tt3
from (Test1 t1
inner join (select tt1, max(tt2) as tt2 from Test2 group by tt1) as X
on (t1.t1 = X.tt1))
inner join (select tt1, tt2, tt3 from Test2 ) as Y
on (X.tt1 = Y.tt1 and X.tt2 = Y.tt2)
order by t1, tt1

いろんなやり方があると感じました。
誠に有難う御座いました。

回答No.3

1050 円（@1050YEN）

＞条件1.テーブル2.TT1はテーブル1.T1に存在するものに限る

失礼しました
LEFT JOIN
ではなく
INNER JOIN
ですね　（ーー；

お礼

有難う御座います。
参考にさせていただきます。

回答No.2

1050 円（@1050YEN）

kensiroooさんの方法で、間違っているわけではありませんが、難しく考え過ぎているように思ったので、書き込みます。

先頭の
>select t1.t1, X.tt1, X.tt2, Y.tt3
を見る限り、「t1.t1」だけがTable1を直接指し、あとは副問い合わせによる結果を参照していますよね。

「X」「Y」は、それぞれの機能を満たしているので、それ相応の意味はあるのでそれはそれでよいと思います。

私はあちこちの会社に転々としているのですが、、、会社によっては副問い合わせを利用する場合、それらの一機能毎に仕様書反映させるところもありました。
要は、仕様書に書いたらどうなるかを考えながら構築すると、何が必要で何が不要かがわかりやすくなると思います。
それに勤めていると、極力無駄設計を行わないようになってきて、シンプルイズベストでいけるようになると思います。

前置きはこれぐらいにしておいてと、、、

私がこの機能の仕様書を書いた場合
１．テーブル1.T1とテーブル2.TT1の結合を行い表示する
ただし以下に条件を要する
　条件1.テーブル2.TT1はテーブル1.T1に存在するものに限る
　条件2.テーブル2.TT2は同一テーブル2.TT1内においての最大の値に限る

※1、単純に「Test1」のキーに該当する「Test2」を全てを取得
SELECT
Test1.t1,Test2.tt1,Test2.tt2,Test2.tt3

FROM
Test1
LEFT JOIN
Test2
ON Test1.t1 = Test2.tt1

※2、ただし「Test2」で必要とする該当条件はこれ
SELECT tt1,MAX(tt2)
FROM Test2
GROUP BY tt1

なので単純に考えたら、EXISTSを利用して、存在チェックをかけるだけです。
私の環境にはオラクルしかないですが、オラクルじゃなくてもＳＱＬサーバでも動くと思います。

SELECT
Test1.t1,Test2.tt1,Test2.tt2,Test2.tt3

FROM
Test1
LEFT JOIN
Test2
ON Test1.t1 = Test2.tt1

WHERE EXISTS(
SELECT tt1
FROM Test2 dmy
GROUP BY tt1
HAVING dmy.tt1 = Test2.tt1
AND MAX(dmy.tt2)= Test2.tt2
)

お礼

本当にいろんなやり方があるもんですね。
>>それに勤めていると、極力無駄設計を行わないようになってきて、シンプルイズベストでいけるようになると思います。
自分は一箇所にずっといるので、周りの状況が
手に入るにこのような機会はすばらしいと思っている一人です。

前置きはこれぐらいにしておいてと、、、

正直な所、Test2．tt3に囚われてしまって変な書き方になってしまいました。
また、自分のソースでは、SELECTを修正する場合に、内部まで
触らなければいけなくなるので、お二方のソースがベストでしょう。

どうなんでしょうね？自分は、まだまだの人間なんで
あっちこっち見渡していては、置いてけぼりになりそうなんで、
どちらかを選んで進みたいものですが・・・・