２つのテーブルで一致しているものを抽出する。SQL文で作成。

OS:WindowsXP
SQLServer2000

＜質問＞
SQL文が浮かばなくて困っています。
どなかたお力を貸していただけないでしょうか。

以下、やりたいことです。
【条件】
１　AテーブルとBテーブルで郵便番号が一致しているものを抽出
２　AテーブルとBテーブルに「番号」列を追加し、初期値を「０」とする
３　１の抽出後、同じ郵便番号がBテーブルに複数あった場合、
　　２で追加した「番号」列を０→”抽出した行分の数字”にする。

一応、【条件】１の箇所（下記）は浮かんだのですが、
２と３が分かりません。

select　Aテーブル.*,Bテーブル.*　from　Aテーブル,Bテーブル
left outer join　Aテーブル　on　Aテーブル.郵便番号 = Bテーブル.郵便番号
WHERE ？？？


●Aテーブル
ID 　｜　名前　｜　郵便番号　　｜　番号
-----------------------------------------------------------------
01　｜　 小林　｜　101-1000　｜　0
02　｜　 鈴木　｜　102-1000　｜　0
03　｜　田中　 ｜　103-1000　｜　0
04　｜　町田　 ｜　104-1000　｜　0
05　｜　木村　 ｜　105-1000　｜　0
06　｜　中田　 ｜　106-1000　｜　0

●Bテーブル
ID　｜　分類　　｜　郵便番号　　　｜　番号
---------------------------------------------------------------------
01　｜　AAA　　｜ 　101-5555　 ｜　0
03　｜　CCC　　｜　103-1000　 ｜　0
04　｜　DDD　　｜　104-1000　　｜　0
05　｜　EEE　　｜　105-1000　　 ｜　 0
06　｜　FFF　　｜ 　105-1000　　 ｜　0
07　｜　GGG　　｜　106-1000　　｜　0
08 　｜　HHH　　｜ 106-1000　　 ｜　0
09　｜　KKK　　｜ 106-1000　　 ｜　0

↓出したい結果↓
ID 　｜　名前　｜　郵便番号　　　｜　分類　 ｜　番号
-------------------------------------------------------------------------------
03　｜　田中　｜　103-1000　　｜　CCC 　 ｜　 0
04　｜　町田　｜　104-1000　　｜　DDD 　｜　 0
05　｜　木村　｜　105-1000　　｜　EEE　 ｜　 2
05　｜　木村　｜　105-1000　　｜　FFF　 ｜　 2
06　｜　中田　｜　106-1000　　｜　GGG　 ｜　 3
06　｜　中田　｜　106-1000　　｜　HHH　 ｜　 3
06　｜　中田　｜　106-1000　　｜　KKK　 ｜　 3

以上になります。
どうぞよろしくお願い致します。

No.3 ベストアンサー 回答者： jamshid6 回答日時： 2009/10/26 21:46

#2です。

cというのは、よく見ていただくとわかりますが、
SELECT 郵便番号,COUNT(*) 番号
FROM Bテーブル
GROUP BY 郵便番号
HAVING COUNT(*)>1
というクエリの結果自体に別名をつけたものですので、
別に新しいテーブルは必要ありません。

そのままいけるでしょう。

この回答へのお礼

お礼が遅くなって申し訳ございません。
教えていただいた通り実行したらできました！

ありがとうございました！助かりました！

お礼日時：2009/10/27 23:17

No.2 回答者： jamshid6 回答日時： 2009/10/26 15:51

PARTITION BYは分析関数で、SQL Server 2005以降でしか使えません。

したがって、SQL Server 2000でやるなら以下の通りになるでしょう。

SELECT
a.ID,
a.名前,
a.郵便番号,
b.分類,
ISNULL(c.番号,0) 番号
FROM Aテーブル a
INNER JOIN Bテーブル b ON b.郵便番号=a.郵便番号
LEFT OUTER JOIN
(SELECT 郵便番号,COUNT(*) 番号 FROM Bテーブル
GROUP BY 郵便番号
HAVING COUNT(*)>1) c ON c.郵便番号=a.郵便番号

この回答へのお礼

ありがとうございます。

試したいのですが、c.番号が？？です。
新たなテーブル（Cのテーブル）を作らないと求められないということでしょうか。

知識不足で申し訳ございません。。

お礼日時：2009/10/26 20:18

No.1 回答者： o123459876 回答日時： 2009/10/26 09:46

select

case
count(*)over(PARTITION BY Aテーブル.ID)
when 1 then 0 else
count(*)over(PARTITION BY Aテーブル.ID)
end as 番号
from　Aテーブル,Bテーブル
left outer join　Aテーブル　on　Aテーブル.郵便番号 = Bテーブル.郵便番号

番号だけ求めました。
実行はしていません。

この回答へのお礼

ありがとうございます。

PARTITIONの存在を今回初めて知り、いまいち使い方が分かりません。

上記のWHERE句の後に教えて頂いたのを追記しましたが、できませんでした。

他に方法がありましたら教えて頂けると助かります。

お礼日時：2009/10/26 12:50