12-2 年下のリーダーを持つ会員を取得

SQLパズル

会員テーブル
会員番号   会員名  年齢  リーダー会員番号
--------  ------  ----  ----------------
001       田中      40   002
002       鈴木      30   002
003       佐藤      25   002
004       福田      40   004
005       渡辺      55   004

年下のリーダーを持つ会員を出力する。

出力結果
会員番号   会員名  年齢  リーダー会員番号
--------  ------  ----  ----------------
001       田中      40   002
005       渡辺      55   004

データ作成スクリプト

create table 会員(
会員番号 char(3),
会員名   char(4),
年齢     number(2),
リーダー会員番号 char(3));

insert into 会員 values('001','田中',40,'002');
insert into 会員 values('002','鈴木',30,'002');
insert into 会員 values('003','佐藤',25,'002');
insert into 会員 values('004','福田',40,'004');
insert into 会員 values('005','渡辺',55,'004');
commit;

SQL

col 会員番号 for a8
col 会員名 for a6
col 年齢 for 9999
col リーダー会員番号 for a16

--■■■自己結合を使う方法■■■
select a.会員番号,a.会員名,a.年齢,a.リーダー会員番号
  from 会員 a,会員 b
 where a.リーダー会員番号 = b.会員番号
   and a.年齢 > b.年齢;

--■■■相関サブクエリを使う方法■■■
select 会員番号,会員名,年齢,リーダー会員番号
  from 会員 a
 where 年齢 > (select b.年齢
                 from 会員 b
                where b.会員番号 = a.リーダー会員番号);

--■■■existsを使う方法■■■
select 会員番号,会員名,年齢,リーダー会員番号
  from 会員 a
 where exists(select 1 from 会員 b
               where b.会員番号 = a.リーダー会員番号
                 and b.年齢 < a.年齢);

--■■■分析関数を使う方法■■■
select 会員番号,会員名,年齢,リーダー会員番号
from (select 会員番号,会員名,年齢,リーダー会員番号,
      max(decode(会員番号,リーダー会員番号,年齢))
      over(partition by リーダー会員番号) as リーダー年齢
      from 会員)
where 年齢 > リーダー年齢;

解説

分析関数を使えば、
テーブルアクセスが1回で済みます。

---