在多次并集和联接操作之后求和列

Question

我想根据某支球队的球员对一列的总数求和。我有一张球员表和一张球队表。目前，我没有任何问题，只是由于某种原因，它不会对我的查询的最后一部分进行汇总。下面是我的代码示例：

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

如果有任何关于为什么会发生这种情况的想法，或者有更好的潜在方式来完成这件事，我们将不胜感激！

Answer 1

您的查询似乎不完整，您必须使用UNION ALL来获得全部总数(如果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，即假定为"不同"。

这可能更有效：

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    ;