SQLでテーブルを結合した結果を取り出し

質問

SQLで以下の様なテーブルがあります。
tableA、tableB、tableCを使って、IDとNUMとRANKを結合し、
ID毎に、
RANKが１つでもあったら、”○”でその数を
RANKが１つもなかったら、”×”で0を
取り出したい。
「oracle10g」です。

-- tableA
ID,NUM,NAME,・・・・・
000001,001,
000001,002,
000001,003,
000002,001,
000003,001,
000003,002,

-- tableB
ID,NUM,RANK
000001,001,A
000001,001,B
000001,001,C
000001,002,A
000001,003,C
000002,001,B
000002,002,B
000003,001,A
000003,002,A
000003,002,C

--tableC
RANK,NAME
A,
B,
C,

結果として、
ID,RANK,判定,個数
000001,A,○,2
000001,B,○,1
000001,C,○,2
000002,A,×,0
000002,B,○,2
000003,A,○,2
000003,B,×,0
000003,C,○,1
を得たいです。

どのようなSQLにしたらよいか、よろしくお願いします。

回答No.5

licht_gkk · ベストアンサー

===============================================
with
tableA as (
select '000001' as ID,'001' as NUM,'TEST1' as NAME from dual union all
select '000001' as ID,'002' as NUM,'TEST1' as NAME from dual union all
select '000001' as ID,'003' as NUM,'TEST1' as NAME from dual union all
select '000002' as ID,'001' as NUM,'TEST2' as NAME from dual union all
select '000002' as ID,'002' as NUM,'TEST2' as NAME from dual union all
select '000003' as ID,'001' as NUM,'TEST3' as NAME from dual union all
select '000003' as ID,'002' as NUM,'TEST3' as NAME from dual
), tableB as (
select '000001' as ID,'001' as NUM,'A' as RANK from dual union all
select '000001' as ID,'001' as NUM,'B' as RANK from dual union all
select '000001' as ID,'001' as NUM,'C' as RANK from dual union all
select '000001' as ID,'002' as NUM,'A' as RANK from dual union all
select '000001' as ID,'003' as NUM,'C' as RANK from dual union all
select '000002' as ID,'001' as NUM,'B' as RANK from dual union all
select '000002' as ID,'002' as NUM,'B' as RANK from dual union all
select '000003' as ID,'001' as NUM,'A' as RANK from dual union all
select '000003' as ID,'002' as NUM,'A' as RANK from dual union all
select '000003' as ID,'002' as NUM,'C' as RANK from dual
), tableC as (
select 'A' as RANK,'基A' as NAME from dual union all
select 'B' as RANK,'基B' as NAME from dual union all
select 'C' as RANK,'基C' as NAME from dual
), IDRANK as (
select A.ID, C.RANK
from tableA A, tableC C
group by A.ID, C.RANK
)
select
T.ID,
T.RANK,
case count(B.ID) when 0 then '×' else '○' end as 判定,
count(B.ID) as 個数
from IDRANK T
left join tableB B on
B.ID = T.ID and
B.RANK = T.RANK
group by T.ID, T.RANK
order by T.ID, T.RANK
===============================================

===============================================
ID,RANK,判定,個数
000001,A,○,2
000001,B,○,1
000001,C,○,2
000002,A,×,0
000002,B,○,2
000002,C,×,0
000003,A,○,2
000003,B,×,0
000003,C,○,1
===============================================
私の環境では上記の結果になります。
（RANKの"D","E"は使用されていないようなので除外しています）
求められている結果と一致しているかと思うのですが・・・

お礼

ありがとうございます。

同じ結果となりました。

回答No.4

licht_gkk

with句での仮想データで一致するのなら実テーブルのデータが異なるだけだと思います。
以下の３つのSQL結果を教えてもらえれば原因確認程度はお手伝い出来るかと思います。

select ID,NUM,NAME from tableA

select ID,NUM,RANK from tableB

select RANK,NAME from tableC

お礼

ありがとうございます。

3つのテーブルの結果です。
-- tableA
ID,NUM,NAME,
000001,001,TEST1
000001,002,TEST1
000001,003,TEST1
000002,001,TEST2
000002,002,TEST2
000003,001,TEST3
000003,002,TEST3

-- tableB
ID,NUM,RANK
000001,001,A
000001,001,B
000001,001,C
000001,002,A
000001,003,C
000002,001,B
000002,002,B
000003,001,A
000003,002,A
000003,002,C

--tableC
RANK,NAME
A,基A
B,基B
C,基C
D,初1
E,初2

回答No.3

licht_gkk

=========================================================
with
tableA as (
select '000001' as ID,'001' as NUM,'' as NAME from dual union all
select '000001' as ID,'002' as NUM,'' as NAME from dual union all
select '000001' as ID,'003' as NUM,'' as NAME from dual union all
select '000002' as ID,'001' as NUM,'' as NAME from dual union all
select '000003' as ID,'001' as NUM,'' as NAME from dual union all
select '000003' as ID,'002' as NUM,'' as NAME from dual
), tableB as (
select '000001' as ID,'001' as NUM,'A' as RANK from dual union all
select '000001' as ID,'001' as NUM,'B' as RANK from dual union all
select '000001' as ID,'001' as NUM,'C' as RANK from dual union all
select '000001' as ID,'002' as NUM,'A' as RANK from dual union all
select '000001' as ID,'003' as NUM,'C' as RANK from dual union all
select '000002' as ID,'001' as NUM,'B' as RANK from dual union all
select '000002' as ID,'002' as NUM,'B' as RANK from dual union all
select '000003' as ID,'001' as NUM,'A' as RANK from dual union all
select '000003' as ID,'002' as NUM,'A' as RANK from dual union all
select '000003' as ID,'002' as NUM,'C' as RANK from dual
), tableC as (
select 'A' as RANK, '' as NAME from dual union all
select 'B' as RANK, '' as NAME from dual union all
select 'C' as RANK, '' as NAME from dual
), IDRANK as (
select A.ID, C.RANK
from tableA A, tableC C
group by A.ID, C.RANK
)
select
T.ID,
T.RANK,
case count(B.ID) when 0 then '×' else '○' end as 判定,
count(B.ID) as 個数
from IDRANK T
left join tableB B on
B.ID = T.ID and
B.RANK = T.RANK
group by T.ID, T.RANK
order by T.ID, T.RANK
=========================================================

tableA/B/Cをwith句で仮想的に生成し、
私の環境(11gまたは9i)で上記のSQLを実行した結果が以下です。
=========================================================
000001,A,○,2
000001,B,○,1
000001,C,○,2
000002,A,×,0
000002,B,○,2
000002,C,×,0
000003,A,○,2
000003,B,×,0
000003,C,○,1
=========================================================
今10g環境がないので10gでは試してませんが、
これでいけないとなるとレコード数が違っているか10gでSQL解釈が異なるとかが考えられるかと思うのですがtableA/B/Cのレコード数は上記のwith句内と一致してるでしょうか？

お礼

ありがとうございます。

10g環境で、記載していただいたSQLを実行しました。
同じ結果となりました。

回答No.2

licht_gkk

いくつか確認させて下さい。

・tableA/B/Cの情報は質問の内容だけで間違いないでしょうか？
・見落としてましたがNUMは結合条件に必要ですか？(IDだけで連結してました)
・下記SQL実行結果をコピペしてもらう事は可能ですか？

select *
from (
select A.ID, C.RANK
from tableA A, tableC C
group by A.ID, C.RANK
) T
left join tableB B on
B.ID = T.ID and
B.RANK = T.RANK
order by T.ID, T.RANK

お礼

ありがとうございます。
>tableA/B/Cの情報は質問の内容だけで間違いないでしょうか？
tableA/B/Cの情報は質問の内容以外もあります。
住所、電話番号、日付等などもあります。

抜粋ですか以下の通りです。
000001,A,000001,0001
000001,B,000001,0001
000001,C,000001,0001
000001,D,NULL,NULL
000002,A,NULL,NULL
000002,B,000002.0002
000002,C,NULL,NULL
という具合です。

回答No.1

licht_gkk

with IDRANK as (
select A.ID, C.RANK
from tableA A, tableC C
group by A.ID, C.RANK
)
select
T.ID,
T.RANK,
case count(B.ID) when 0 then '×' else '○' end as 判定,
count(B.ID) as 個数
from IDRANK T
left join tableB B on
B.ID = T.ID and
B.RANK = T.RANK
group by T.ID, T.RANK
order by T.ID, T.RANK

こんな感じでしょうか。
10gだったらwith句使えた気がしますが使えなかった場合は

select
T.ID,
T.RANK,
case count(B.ID) when 0 then '×' else '○' end as 判定,
count(B.ID) as 個数
from (
select A.ID, C.RANK
from tableA A, tableC C
group by A.ID, C.RANK
) T
left join tableB B on
B.ID = T.ID and
B.RANK = T.RANK
group by T.ID, T.RANK
order by T.ID, T.RANK

これでいけると思います。

お礼

ありがとうございます。

確認してみたのですが、
個数が、tableAのID数分となります。
”000001”の”A”はtableBでは”2”つしかないのに
”3”となります。
”000002"の”A”はtableBでは存在しないのに
”1”となります。
また、tableBにまったくレコードががないIDの場合、
”0”ではなく、”1”となります。

よろしくお願いします。