こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

OS:WindowsXP
SQLServer2000

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

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

一応、【条件】1の箇所(下記)は浮かんだのですが、
2と3が分かりません。

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

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

投稿日時 - 2009-10-26 00:10:33

QNo.5396837

すぐに回答ほしいです

質問者が選んだベストアンサー

#2です。

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

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

投稿日時 - 2009-10-26 21:46:07

お礼

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

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

投稿日時 - 2009-10-27 23:17:29

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

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.郵便番号

投稿日時 - 2009-10-26 15:51:28

お礼

ありがとうございます。

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

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

投稿日時 - 2009-10-26 20:18:35

ANo.1

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テーブル.郵便番号

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

投稿日時 - 2009-10-26 09:46:15

お礼

ありがとうございます。

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

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

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

投稿日時 - 2009-10-26 12:50:49

あなたにオススメの質問