mysql - 关于mysql INNER JOIN的问题

Question

我正在尝试按照查询在用户页面上显示我的所有用户...

SELECT u.id, u.username, u.email, u.active, u.admin,
u.team_id, t.name AS team_name, sum( s.score ) AS total_score
FROM users u
INNER JOIN teams t ON u.team_id = t.id
INNER JOIN stats s ON u.id = s.user_id

我的用户表中有 4 个用户，我想列出所有用户，但如果我使用 INNER JOIN，它只返回 1 行。检查数据结构如下...

| id | game | user_id | rank | score | dnt |
+----+------+---------+-------+------+-----+
|  1 | test | 5       | 2    | 2200  | 
+--------+----------+----------+-----+-----+
|  2 | test | 3       | 1    | 2500  | 
+--------+----------+----------+-----+-----+

团队

| id | name  | dnt |
+----+-------+-----+
|  1 | team1 |     |
+----+-------+-----+
|  2 | team2 |     |
+----+-------+-----+

用户

| id | username | email | team_id |
+----+----------+-------+---------+
|  1 | user1    |       | 1       |
+----+----------+-------+---------+
|  1 | user2    |       | 2       |
+----+----------+-------+---------+

Answer 1

您需要使用 GROUP BY 以便将分数的总和拆分给不同的用户，此外，如果您使用 LEFT JOIN 那么您一定会得到一行对于每个用户实例，即使它在统计表中没有行(然后，按照 regilero 建议使用 COALESCE，总和将为 0)

类似于:

SELECT u.id, u.username, u.email, u.active, u.admin,
u.team_id, t.name AS team_name, sum( COALESCE(s.score) ) AS total_score
FROM users u
LEFT JOIN teams t ON u.team_id = t.id
LEFT JOIN stats s ON u.id = s.user_id
GROUP BY u.id, u.username, u.email, u.active, u.admin,
u.team_id, t.name

这还没有测试