我想总结基于属于某个团队的球员的总列数。我有一个球员桌和一个团队桌。目前,我没有任何问题,只是由于某种原因它不会对查询的最后一部分求和。这是我的代码的示例:
Select SUM(points)
from (select points
from player
Join team on player.full_name=team.player1
Where team.team_id = 8
and week =9
UNION
Select points
FROM player
JOIN team on player.full_name=team.player2
Where team.team_id = 8
and week =9
UNION
Select points
FROM player
JOIN team on player.full_name=team.player3
Where team.team_id = 8
and week =9
UNION
Select points
FROM player
JOIN team on player.full_name=team.player4
Where team.team_id = 8
and week =9
任何关于为什么会发生这种情况的想法,或者有更好的潜在方法可以解决这一问题,将不胜感激!
最佳答案
您的查询似乎不完整,您必须使用UNION ALL
来获得总计(f 2或更多玩家具有相同的点数UNION DISTINCT
会消除这些行):
SELECT
SUM( points )
FROM (
SELECT
points
FROM player
JOIN team ON player.full_name = team.player1
WHERE team.team_id = 8
AND week = 9
UNION ALL
SELECT
points
FROM player
JOIN team ON player.full_name = team.player2
WHERE team.team_id = 8
AND week = 9
UNION ALL
SELECT
points
FROM player
JOIN team ON player.full_name = team.player3
WHERE team.team_id = 8
AND week = 9
UNION ALL
SELECT
points
FROM player
JOIN team ON player.full_name = team.player4
WHERE team.team_id = 8
) d
但我相信您的团队表需要更改以提高效率
请注意,使用
UNION
= UNION DISTINCT
,即如果省略则假定为“ distinct”。这可能会更有效:
SELECT
SUM( player.points )
FROM player
WHERE player.full_name IN (
SELECT distinct
case when cj.n = 1 then team.player1
when cj.n = 2 then team.player2
when cj.n = 3 then team.player3
when cj.n = 4 then team.player4
end
FROM team
cross join (
select 1 as n union all
select 2 as n union all
select 3 as n union all
select 4 as n
) cj
WHERE team.team_id = 8
)
AND player.week = 9 ;