SQLでテーブル結合してID毎にRANKの有無を判定する方法

質問

• SQLを使用して、tableA、tableB、tableCを結合し、ID毎にRANKの有無を判定する方法について説明します。
• 結果として、IDとRANKを表示し、RANKが存在する場合は'○'とその数を、存在しない場合は'×'と0を表示します。
• 適切な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"は使用されていないようなので除外しています）
求められている結果と一致しているかと思うのですが・・・

お礼

ありがとうございます。

同じ結果となりました。