SELECT文で

質問

質問させていただきます。
下記のようなデータがあります。

表.TEST
コード1　
　0001
　0002
　0003
　0004
表.TEST2
コード1　　　コード２　階数　・・・・
　0001　　　　401　　　　4　　　
　0002　　　　501　　　　5
　0002 401 4
　0003　　　　301　　　　3
0003　　　　101　　　　1

以上のテーブルを内部結合して以下のようなデータを抽出します。
コード1　　　コード２　階数　・・・・
　0001　　　　401　　　　4　　　
　0002　　　　501　　　　5
　0003　　　　301　　　　3
0001　　　　101　　　　1 ←この部分を削除したい。

以上わかる方いたら教えてください・

回答No.4

noname#140971 · ベストアンサー

チト、未完成でした。

____|建物コード|部屋コード|階数|
__1|___________1|401_________|_____4|
__2|___________2|501_________|_____5|
__3|___________3|202_________|_____3|
__4|___________3|301_________|_____3|

SELECT tab1.[建物コード],tab1.[部屋コード],tab1.[階数] FROM
(SELECT Max(階数) AS MAX_FLOOR, [建物コード] FROM tab1 GROUP BY [建物コード]) As X
LEFT JOIN tab1 ON (X.[建物コード] = tab1.建物コード) AND (MAX_FLOOR = tab1.階数);

ここまではOKだと思います。
問題は、[部屋コード]と[階数]とで条件を設定すれば矛盾が生じることでしょう。
ならば、次のような列[RF]を生成したらどうでしょうか？

____|建物コード|部屋コード|階数|RF_____|

__1| ___________1|401_________|_____4|49599|
__2|____________2|401_________|_____4|49599|
__3|____________2|501_________|_____5|59499|
__4|____________3|101_________|_____1|19899|
__5|____________3|202_________|_____3|39798| <-- 最大値
__6|____________3|301_________|_____3|39699|

試しに、X を生成するSELECT文を実行してみます。

___|建物コード|MAX_FLOOR|MAX_RF|

__1|___________1|_______________4|__49599|
__2|___________2|_______________5|__59499|
__3|___________3|_______________3|__39798|

SELECT [建物コード],Max(階数) AS MAX_FLOOR, Max([階数]*10000+(10000-[部屋コード])) AS MAX_RF FROM tab1 GROUP BY [建物コード]

これで、首尾よく[階数]最大で[部屋コード]最小を抜き出しています。
ここまでくれば、[MAX_RF]を手掛かりに「部屋コード]と[階数]を特定することが出来るかと思います。

お礼

返答ありがとうございます。
一応自分なりに考えてついさっき完成できました。
Husky2007さんに前回頂いたやり方や、職場の方などにやり方を聞いて
参考にしてやりました。
SELECT A.建物コード, A.部屋コード, A.階数 FROM tab1 A RIGHT OUTER JOIN (SELECT 建物コード, MAX(階数コード) AS MAXFLOOR FROM T_FBROOM GROUP BY 建物コード) B ON A.建物コード = B.建物コード 　AND A.建物コード = B.MAXFLOOR WHERE (A.建物コード IN (SELECT DISTINCT MAX(建物コード) FROM tab1 WHERE A.建物コード = 建物コード AND A.階数 = 階数 GROUP BY 建物コード)) ORDER BY A.建物コード,　A.部屋コード

以上で自分の思い通りの取得が出来ました。

それにしてもいろいろな方法があって凄い勉強になります。
今回のHusky2007さんのやり方は考えつけなかったです。（＞＜）
わかりやすい解説も助かります。

丁寧な解説等本当にありがとうございました。
また解らない事があった場合、質問させていただくと思いますが
その時はよろしくお願い致します。

回答No.3

noname#140971

チクッと横から失礼します・・・。

tab1:

建物コード__部屋コード__階数
_______0001__________401_______4
_______0002__________401_______4
_______0002__________501_______5
_______0003__________101_______1
_______0003__________301_______3

SELECT tab1.[建物コード],tab1.[部屋コード],tab1.[階数] FROM
(SELECT Max(階数) AS MAX_FLOOR, [建物コード] FROM tab1 GROUP BY [建物コード]) As X
LEFT JOIN tab1 ON (X.[建物コード] = tab1.建物コード) AND (MAX_FLOOR = tab1.階数);

SQL Server 2000 でのこのSQL文の実行結果は次のようです。

|建物コード|部屋コード|階数|

|________0001|________401|_____4|
|________0002|________501|_____5|
|________0003|________301|_____3|

.[建物コード] 毎の[階数]のMAXを求め、それを結合条件にしたのでしたら、表は一つで事足りると思いますが・・・。

|建物コード|部屋コード|階数|

|________0001|________401|_____4|
|________0002|________501|_____5|
|________0003|________202|_____3|

|________0003|________301|_____3|

もちろん、先のSQL文ではこのような結果になります。

SELECT tab1.[建物コード],tab1.[部屋コード],tab1.[階数] FROM
(SELECT Max(階数) AS MF, Max([部屋コード]) AS MR, [建物コード] FROM tab1 GROUP BY [建物コード]) As X
LEFT JOIN tab1 ON (X.[建物コード]=tab1.[建物コード]) AND (MF=tab1.階数) AND(MR=tab1.[部屋コード])

[.[部屋コード]の抽出条件に最大値ないし最小値を指定するのか、それともしないのかは更に検討されたらと・・・。
いずれにしろ、表は一つで事足りると思いますが・・・。

お礼

ありがとうございます。
情報の足りない部分がありましたので補足致します。
建物コード　int 　　 長さ4　
部屋コード　varchar　長さ5　
階数　　　　int 　　 長さ4
です。

これは後から見つけたのですが、
データの中に
建物コード　　部屋コード　　階数
__42511__________1140________11_ ←階数の最大値
__42511___________938_________9_ ←部屋コードの最大値

といったデータがあり、どちらも選択できない事象が
発生しています。
結果として、
階数が最大、階数の中で最大か最小か(まだ検討中）の部屋コード
という形で取得したいと考えています。
上の例で言えば部屋コード1140のデータが取れると望ましいです。
あと、教えていただいた文章を試したのですが、表示させると上手く
並んでいるのですが、所々NULLが入ってしまいます。データは存在するのですが・・・

建物コード　　部屋コード　　階数
___29__________＿301__________3_
__NULL___________NULL_______NULL
___31__________＿705__________7_

実際の30のデータ
建物コード　　部屋コード　　階数
___30____________1006________10_
___30_____________101_________1_
___30__________＿_102_________1_
___30_____________203_________2_
___30__________＿_504_________5_
___30_____________705_________7_

以上補足でした。

補足

表1と表2それぞれの項目で表示させたい項目がありまして・・・
それで表１と表2を結合させるという方法を取っております。

以上、補足させていただきます。

回答No.2

chukenkenkou

再度の補足説明をお願いします。

(1)同じ建物コードで同じ階数の場合、どちらを残すのでしょうか？
　部屋コードが最小のものを残すのですか？

(2)RDBMSは、ここのカテゴリ通りSQL Serverですか？
　バージョンは何でしょうか？
もしSQL Server 2005なら、順序付けを分析関数使って容易に行えるので
　教えてください。

補足

(1)部屋コードが最小の物を残す予定です。

(2)SQL Server 2000です。

以上補足させていただきます。

回答No.1

chukenkenkou

提示されたデータ例に、間違いはないですか？
本当に内部結合できていますか？

提示データの確認と、どういうSQLを考えたのか見せてください。

補足

説明させていただきます。

上のデータだと解りにくいところが有りますので
変えます。

表1
建物コード　・・・・・
00001
00002
00003
00004

表2
建物コード　部屋コード　階数
00001 202 2
00001 301 3
00002 401 4
00003 501 5
00004 204 2
00004 205 2

結合後
表1建物コード・・・ 表2建物コード 部屋コード　階数
00001 　　　00001　　　　　201 2
00001 　　　00001 301 3
00002 　　　00002 401 4
00003 　　　00003 501 5
00004 　　　00004 204 2
00004　 　　　00004 205 2

結合時sql
SELECT b.*, r.* FROM 表1 b INNER JOIN
表2 r ON b.建物コード = r.建物コード

条件で階数で最大階のもだけ表示させるsql
SELECT b.*, r.* FROM 表1 b INNER JOIN
表2 r ON b.建物コード = r.建物コード
(r.FB_CD IN　(SELECT　MAX(階数)
FROM T_FBROOM rs
WHERE
b.建物コード　= rs.建物コード
r.部屋コード　= rs.部屋コード
r.階数　　　　= rs.階数
　GROUP BY FB_BCD))

結果表
表1建物コード・・・ 表2建物コード 部屋コード　階数
00001 　　　00001 0301 3
00002 　　　00002 0401 4
00003 　　　00003 0501 5
00004 　　　00004 0204 2
00004　 　　　00004 0205 2　←この部分

結果から建物コードの重複を無くしたい。

考えたsql
SELECT b.*, r.* FROM 表1 b INNER JOIN
表2 r ON b.建物コード = r.建物コード
(r.階数 IN　(SELECT　MAX(階数)
FROM T_FBROOM rs
WHERE　IN
　　(SELECT MIN(部屋コード)
FROM 　T_FBROOM rs2
WHERE rs.建物コード = rs2.建物コード) AND
b.建物コード　= rs.建物コード
r.部屋コード　= rs.部屋コード
r.階数　　　　= rs.階数
　GROUP BY FB_BCD))

以上補足でした。