トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
9-11 子供が一致する親を取得
SQLパズル
親テーブル
親ID 子供 出産順序
---- ---- -------
1 一郎 1
1 次郎 2
1 三郎 3
2 三郎 1
2 一郎 2
2 次郎 3
3 一郎 1
3 次郎 2
3 三郎 3
4 三郎 1
4 一郎 2
5 太郎 1
5 次郎 2
5 三郎 3
親テーブルから、別の親IDと、
子供の数と、子供の名前と、子供の出産順序が
一致する親IDを出力する
出力結果
親ID 子供 出産順序
---- ---- -------
1 一郎 1
1 次郎 2
1 三郎 3
3 一郎 1
3 次郎 2
3 三郎 3
データ作成スクリプト
create table 親(
親ID number(1),
子供 char(4),
出産順序 number(1));
insert into 親 values(1,'一郎',1);
insert into 親 values(1,'次郎',2);
insert into 親 values(1,'三郎',3);
insert into 親 values(2,'三郎',1);
insert into 親 values(2,'一郎',2);
insert into 親 values(2,'次郎',3);
insert into 親 values(3,'一郎',1);
insert into 親 values(3,'次郎',2);
insert into 親 values(3,'三郎',3);
insert into 親 values(4,'三郎',1);
insert into 親 values(4,'一郎',2);
insert into 親 values(5,'太郎',1);
insert into 親 values(5,'次郎',2);
insert into 親 values(5,'三郎',3);
commit;
SQL
--■■■group byを使う方法1■■■
select 親ID,子供,出産順序
from 親 a
where 親ID in
(select b.親ID
from 親 b,親 c
where b.親ID != c.親ID
and (select count(*) from 親 d where d.親ID=b.親ID)
= (select count(*) from 親 d where d.親ID=c.親ID)
and b.子供=c.子供
and b.出産順序=c.出産順序
group by b.親ID,c.親ID
having count(*) = (select count(*) from 親 d where d.親ID=b.親ID))
order by 親ID,出産順序;
--■■■group byを使う方法2■■■
select 親ID,子供,出産順序
from 親 a
where exists
(select 1 from 親 b,親 c
where b.親ID = a.親ID
and b.親ID != c.親ID
and (select count(*) from 親 d where d.親ID=b.親ID)
= (select count(*) from 親 d where d.親ID=c.親ID)
and b.子供=c.子供
and b.出産順序=c.出産順序
group by b.親ID,c.親ID
having count(*) = (select count(*) from 親 d where d.親ID=b.親ID))
order by 親ID,出産順序;
--■■■階層問い合わせとConnect_by_IsLeafを使う方法(10g以降)■■■
with WorkView as (
select 親ID,substr(sys_connect_by_path(子供,','),2) as 子供リスト
from 親
where Connect_by_IsLeaf = 1
start with 出産順序 = 1
connect by prior 出産順序 = 出産順序 -1
and prior 親ID = 親ID)
select 親ID,子供,出産順序
from 親 a
where exists(select 子供リスト from WorkView b
where b.親ID = a.親ID
intersect
select 子供リスト from WorkView b
where b.親ID != a.親ID)
order by 親ID,出産順序;
--■■■within groupを使う方法■■■
select 親ID,子供,出産順序
from 親 a
where exists(select 1
from 親 b,親 c
where b.親ID = a.親ID
and c.親ID != a.親ID
group by b.親ID,c.親ID
having count(case when b.子供 = c.子供
and b.出産順序 = c.出産順序
then 1 end)
= all(-1+Dense_Rank(null,null) within group(order by b.子供,b.出産順序),
-1+Dense_Rank(null,null) within group(order by c.子供,c.出産順序)));
解説