我有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 |
--------------
我想在我的排行榜中只看到約翰和威利
uj5u.com熱心網友回復：
像這樣的東西？（第 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>
uj5u.com熱心網友回復：
感謝@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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/316307.html
下一篇：SQL查詢花費的時間太長