问使用输入条件填充排行榜的查询

EN

SQL> with
  2  all_goals (type, cgroup, points) as
  3    (select 'A', 'IMPORTANT', 100 from dual union all
  4     select 'B', 'IMPORTANT', 200 from dual union all
  5     select 'C', 'OTHER'    ,  10 from dual union all
  6     select 'D', 'OTHER'    ,  10 from dual
  7    ),
  8  goals_reached (type, earned_points, player_name) as
  9    (select 'A', 100, 'John'  from dual union all
 10     select 'B', 200, 'Willy' from dual union all
 11     select 'C',  10, 'Bob'   from dual union all
 12     select 'D',  10, 'Bob'   from dual
 13    )
 14  select r.player_name
 15  from goals_reached r join all_goals g on g.type = r.type
 16  where g.cgroup = 'IMPORTANT'
 17    and g.points > 0;

PLAYE
-----
John
Willy

SQL>

select gr1.PLAYER_NAME, sum(gr1.EARNED_POINTS)
from GOALS_REACHED gr1
where exists(
  select *
  from GOALS_REACHED gr2
       inner join ALL_GOALS ag
       on gr2.TYPE=ag.TYPE
  where ag.GROUP='IMPORTANT' 
        and gr1.PLAYER_NAME=gr2.PLAYER_NAME
)
group by gr1.PLAYER_NAME