9-36 卒業可能な学生を出力

SQLパズル

Categoriesテーブル
category  rqd_credits
--------  -----------
    A         10
    B          3
    C          5

CreditsEarnedテーブル
student  category  credits
-------  --------  -------
    Bob     A          2
    Bob     C          2
    Bob     A         12
    Bob     C          4
    Joe     A          3
    Joe     A          2
    Joe     A          3
    Joe     A          3
    Joe     B          3
    Joe     C          3
    Joe     C          2
    Joe     C          3
   John     A          1
   John     B        100
   Mary     A          5
   Mary     A          6
   Mary     B          7
   Mary     C          4
   Mary     C          4

CreditsEarnedテーブルの
studentごとのcategoryの合計が、
全てCategoriesテーブルのrqd_credits以上なら、GradにXを
そうでないなら、NoGradにXを
出力する

出力結果
student  Grad  NoGrad
-------  ----  ------
    Bob  null    X
    Joe   X     null
   John  null    X
   Mary   X     null

SQLパズル(日本語版)のパズル39 [卒業] を参考にさせていただきました
SQLパズル 第2版のパズル43 [卒業] を参考にさせていただきました

データ作成スクリプト

create table Categories(
category    char(1)   not null,
rqd_credits number(2) not null);

insert into Categories values('A',10);
insert into Categories values('B', 3);
insert into Categories values('C', 5);

create table CreditsEarned(
student  varchar2(4) not null,
category char(1)     not null,
credits  number(3)   not null);

insert into CreditsEarned values('Bob','A', 2);
insert into CreditsEarned values('Bob','C', 2);
insert into CreditsEarned values('Bob','A',12);
insert into CreditsEarned values('Bob','C', 4);

insert into CreditsEarned values('Joe','A',3);
insert into CreditsEarned values('Joe','A',2);
insert into CreditsEarned values('Joe','A',3);
insert into CreditsEarned values('Joe','A',3);
insert into CreditsEarned values('Joe','B',3);
insert into CreditsEarned values('Joe','C',3);
insert into CreditsEarned values('Joe','C',2);
insert into CreditsEarned values('Joe','C',3);

insert into CreditsEarned values('John','A',  1);
insert into CreditsEarned values('John','B',100);

insert into CreditsEarned values('Mary','A',5);
insert into CreditsEarned values('Mary','A',6);
insert into CreditsEarned values('Mary','B',7);
insert into CreditsEarned values('Mary','C',4);
insert into CreditsEarned values('Mary','C',4);
commit;

SQL

col Grad for a4
col NoGrad for a4

--■■■相関サブクエリを使う方法■■■
select student,
case when (select count(*) from Categories) = sum((select 1
                                                     from Categories b
                                                    where b.category = a.category
                                                      and b.rqd_credits <= a.credits))
     then 'X' end as Grad,
case when (select count(*) from Categories) > sum((select 1
                                                     from Categories b
                                                    where b.category = a.category
                                                      and b.rqd_credits <= a.credits))
     then 'X' end as nograd
from (select student,category,sum(credits) as credits
        from CreditsEarned
       group by student,category) a
group by student
order by student;

--■■■クロスジョインとグループ化とcase式と分析関数の組み合わせる方法■■■
select distinct a.student,
case when count(distinct b.category) =
sum(case when sum(a.credits) >= max(decode(a.category,b.category,b.rqd_credits))
         then 1 else 0 end) over(partition by a.student)
then 'X' end as grad,
case when count(distinct b.category) >
sum(case when sum(a.credits) >= max(decode(a.category,b.category,b.rqd_credits))
         then 1 else 0 end) over(partition by a.student)
then 'X' end as nograd
  from CreditsEarned a,Categories b
 group by a.student,a.category
 order by a.student;

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select c.student,
case when count(*)  = count(c.category) then 'X' end as Grad,
case when count(*) != count(c.category) then 'X' end as NoGrad
  from Categories a
  Left Join (select b.student,b.category,
             sum(b.credits) as credits
               from CreditsEarned b
             group by b.student,b.category) c
  partition by (c.student)
    on a.category = c.category
   and a.rqd_credits <= c.credits
group by c.student;

--■■■インラインビューで件数を求めておく方法■■■
select b.student,
case when a.cnt = count(case when a.rqd_credits <= b.credits
                             then 1 end)
     then 'X' end as Grad,
case when a.cnt != count(case when a.rqd_credits <= b.credits
                              then 1 end)
     then 'X' end as NoGrad
from (select category,rqd_credits,
      count(*) over() as cnt
      from Categories) a
      Join
     (select student,category,
      sum(credits) as credits
      from CreditsEarned
      group by student,category) b
        on a.category = b.category
group by b.student,a.cnt
order by b.student;

解説

相関サブクエリを使う方法では、
sum関数の中で、相関なスカラー副問い合わせをしてます。

---