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

Question

我有3个表:

PLAYER        (NAME)
ALL_GOALS     (TYPE, GROUP        , POINTS)
GOALS_REACHED (TYPE, EARNED_POINTS, PLAYER_NAME)

我需要写一个查询给我的排行榜。

当且仅当玩家使用GROUP='IMPORTANT'达到至少一个目标时才能进入排行榜。

例如:

+--------------+
| PLAYER       |
+--------------+
| John         |
| Bill         |
| Bob          |
| Willy        |
+--------------+
+-------------------+
| ALL_GOALS         |
+-------------------+
| A, IMPORTANT, 100 |
| B, IMPORTANT, 200 |
| C, OTHER, 10      |
| D, OTHER, 10      |
+-------------------+
+--------------+
| GOALS_REACHED|
+--------------+
| A, 100, John |
| B, 200, Willy|
| C, 10, Bob   |
| D, 10, Bob   |
+--------------+

我希望我的排行榜上只有约翰和威利

Answer 1

像这样?(样本数据在第1 - 13行;查询从第14行开始

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>

Answer 2

感谢@Littlefoot的回答和@mathguy有用的评论，我改进了查询并基于它创建了一个视图。这个看起来最像一个无序的排行榜。

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