10-63 受講履歴を出力

SQLパズル

T個人テーブル              T受講テーブル
個人Code   講座Code        個人Code   受講時間          講座Code   得点
--------   --------        --------  ----------------  --------   ----
       1   00001                  1  2004/07/06 09:00  00001       50
       1   00002                  2  2004/07/06 09:30  00002       75
       1   00003                  2  2004/07/06 10:30  00003      100
       2   00001                  2  2004/07/07 09:30  00002      100
       2   00002
       2   00003           T講座テーブル
       3   00001           講座Code    講座名
       3   00002           --------    ------
       4   00003           00001       国語
                           00002       英語
                           00003       数学

一回でも受講した人の、
全ての講座の受講履歴を出力する

出力結果
個人CODE  授業名   受講回数  受講時間           得点
--------  ------  --------  ----------------   ----
       1  国語           1  2004/07/06 09:00     50
       1  英語           0              null   null
       1  数学           0              null   null
       2  国語           0              null   null
       2  英語           1  2004/07/06 09:30     75
       2  英語           2  2004/07/07 09:30    100
       2  数学           1  2004/07/06 10:30    100

データ作成スクリプト

create table T個人(
個人Code number(1),
講座Code char(5));

insert into T個人 values(1,'00001');
insert into T個人 values(1,'00002');
insert into T個人 values(1,'00003');
insert into T個人 values(2,'00001');
insert into T個人 values(2,'00002');
insert into T個人 values(2,'00003');
insert into T個人 values(3,'00001');
insert into T個人 values(3,'00002');
insert into T個人 values(4,'00003');

create table T講座(
講座Code char(5),
講座名   char(4));

insert into T講座 values('00001','国語');
insert into T講座 values('00002','英語');
insert into T講座 values('00003','数学');

create table T受講(
個人Code number(1),
受講時間 date,
講座Code char(5),
得点     number(3));

insert into T受講 values(1,TO_DATE('2004/07/06 09:00','yyyy/mm/dd hh24:mi'),'00001', 50);
insert into T受講 values(2,TO_DATE('2004/07/06 09:30','yyyy/mm/dd hh24:mi'),'00002', 75);
insert into T受講 values(2,TO_DATE('2004/07/06 10:30','yyyy/mm/dd hh24:mi'),'00003',100);
insert into T受講 values(2,TO_DATE('2004/07/07 09:30','yyyy/mm/dd hh24:mi'),'00002',100);
commit;

SQL

select a.個人Code,
(select b.講座名
   from T講座 b
  where a.講座Code = b.講座Code) as 授業名,
count(c.受講時間)
over(partition by a.個人Code,a.講座Code order by c.受講時間) as 受講回数,
to_char(c.受講時間,'yyyy/mm/dd hh24:mi') as 受講時間,c.得点
 from T個人 a,T受講 c
where a.個人Code = c.個人Code(+)
  and a.講座Code = c.講座Code(+)
  and exists (select 1 from T受講 d
               where d.個人Code = a.個人Code )
order by a.個人Code,a.講座Code,c.受講時間;

解説

count関数を使って、null以外の件数を取得してます

---