SQL 在一个查询中求和两个表

Question

我有一个 users 表，其中包含有关我的用户的所有信息，还有一个 id 列。我还有另外 2 个表格，其中包含每个用户在他们玩的游戏中获得的分数。我们称它们为 game1 和 game2。

db-fiddle

create table users (id text, username text);
create table game1 (user_id text, points int);
create table game2 (user_id text, points int);

一个用户在每个表中可以有多个行。我想总结每个用户得到的所有积分。

这是一个例子:

-- Users table
id      username
user1   Adam
user2   John
user3   Martha

-- game1 table
user_id points
user1   10
user2   9
user1   8
user1   7

--game2 table
user_id points
user2   1
user3   2
user1   3
user1   4

当我尝试使用两个左连接时，我得到重复值和空值...显然我在这里做错了...

这是我的 sql:

-- try to sum all games points for each player
select u.id, u.username, sum(game1.points) + sum(game2.points) as sum_all_games_points
from users u
  LEFT JOIN game1 ON game1.user_id = u.id
  LEFT JOIN game2 ON game2.user_id = u.id
group by u.id, u.username;

这个查询的结果是:

id      username    sum_all_games_points
user2   John        10
user3   Martha      null
user1   Adam        71

我期望得到的结果是这样的(不需要用户 ID):

username    sum_all_games_points
John        32                -- This is the sum of (10 + 8 + 7 + 3 + 4)
Martha      2                 -- Only one game played on game2
Adam        10                -- 9 points from game1 and 1 point from game2

Answer 1

您得到奇怪的结果有几个原因。首先，您的 JOIN 会为每个用户创建多行，因此某些值会被重复计算。其次，您需要使用 COALESCE 将 NULL 值转换为 0，以避免从加法中得到 NULL 输出。这应该有效:

SELECT u.id, u.username, 
       COALESCE(g1.points, 0) + COALESCE(g2.points) AS sum_all_games_points
FROM users u
LEFT JOIN (SELECT user_id, SUM(points) AS points
           FROM game1
           GROUP BY user_id) g1 ON g1.user_id = u.id
LEFT JOIN (SELECT user_id, SUM(points) AS points
           FROM game2
           GROUP BY user_id) g2 ON g2.user_id = u.id

输出:

id      username    sum_all_games_points
user1   Adam        32
user2   John        10
user3   Martha      2

Demo on dbfiddle