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 回答日時： 2008/03/26 08:50

チト、未完成でした。

____|建物コード|部屋コード|階数|
__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さんのやり方は考えつけなかったです。（＞＜）
わかりやすい解説も助かります。

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

お礼日時：2008/03/26 14:53

No.3 回答者： noname#140971 回答日時： 2008/03/24 15:29

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

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.[部屋コード])

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

この回答への補足

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

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

補足日時：2008/03/24 20:32

この回答へのお礼

ありがとうございます。
情報の足りない部分がありましたので補足致します。
建物コード　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_

以上補足でした。

お礼日時：2008/03/26 01:13

No.2 回答者： chukenkenkou 回答日時： 2008/03/24 12:03

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

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

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

この回答への補足

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

(2)SQL Server 2000です。

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

補足日時：2008/03/24 18:07

No.1 回答者： chukenkenkou 回答日時： 2008/03/24 06:57

提示されたデータ例に、間違いはないですか？

本当に内部結合できていますか？

提示データの確認と、どういう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))

以上補足でした。

補足日時：2008/03/24 11:14