我想获取团队的名称和每个团队中每个位置的数量:
name|SG(position name)|PG(position name)|C(position name)|<br>
AAA | 1 | 2 | 4<br>
BBB | 2 | 2 |1<br>
团队表
TEAM_ID NUMBER
NAME VARCHAR
玩家表
PLAYER_ID NUMBER
F_NAME VARCHAR
L_NAME VARCHAR
TEAM_ID NUMBER <- REFERENCE TEAM TABLE
POSITION VARCHAR <- LIKE SG, C, PG and other 5 or 6 positions
我写了一些代码,但无法弄清楚如何创建显示每个团队中职位数量的职位名称列。我找到了它的"透视",但无法编写查询以获得所需的结果。
select team.name, (HOW TO WRITE QUERY?)
from player p
join team t on p.team_id = t.team_id
这可能会解决问题。
Select t.name,
Sum(case when p.position = 'SG' then 1 else 0 end) as SG,
Sum(case when p.position = 'PG' then 1 else 0 end) as PG,
Sum(case when p.position = 'C' then 1 else 0 end) as C
From player p
Join team t using (team_id)
Group by t.name
我希望我正确理解了你的问题。
编辑:
好的,我想我明白了。
Select * from (
Select t.name, p.position, p.player_id
From player p
Join team t using (team_id)
)
Pivot XML
(
Count(player_id)
For position in (select position from player)
)