mysql - 是否可以将 JOIN 添加到此 SQL 语句中？

Question

我有一个表和一个查询，用于返回进球最多的球员列表。

查询效果很好，但是为了让我获得用户名，我必须根据返回的结果 (player_id) 执行另一个查询。

是否可以修改我现有的查询以连接两个表？我知道这对于普通查询是可能的，我只是不确定它是否在这种情况下，因为返回的是自定义结果表。

这是我的初始表，名为 results:

+----------------+-------------+-----+
| Field          | Type        | Key |
+----------------+-------------+-----+
| results_id     | int         | Pri |
| community_id   | int         |     |
| player1_id     | int         |     |
| player1_goals  | int         |     |
| player2_id     | int         |     |
| player2_goals  | int         |     |
+----------------+-------------+-----+

这是我用来返回结果的查询:

select player, sum(goals) from 
((select player1_id as player, player1_goals as goals from results where community_id = 5 ) 
union all 
(select player2_id as player, player2_goals as goals from results where community_id = 5 ) 
) p 
group by player 
order by sum(goals) desc

这就是我返回结果的方式:

+----------------+------------+
| Player         | sum(goals) |
+----------------+------------+
| 2              | 94         |
| 14             | 63         |
| 7              | 43         |
+----------------+------------+

是否可以修改上述查询并向我的users 表添加一个join:

+--------+-----------+
| id     | user_name  |
+---------------------+
| 2      | John       |
| 7      | Andrew     |
| 14     | Charles    |
+--------+------------+

获取输出:

+----------------+----------------+------------+
|user_name       | Player         | sum(goals) |
+----------------+----------------+------------+
| John           | 2              | 94         |
| Charles        | 14             | 63         |
| Andrew         | 7              | 43         |
+----------------+----------------+------------+

Answer 1

您可以使用 join 来完成。您也可以将其表示为:

select u.*,
       (select sum(case when u.id = r.player1_id then r.player1_goals else r.player2_goals)
        from results r
        where r.community_id = 5 and
              u.id in (r.player1_id, r.player2_id)
       ) as goals
from users u;