mysql - 对于存在多个 WHERE 的多个 COLUMNS 的 SUM，MySQL 语法是否正确？

Question

我有以下数据库表:

+----------------+-------------+-----+----- +--------+----------------+
|领域 |类型 |空 |关键|默认 |额外 |
+----------------+-------------+------+-----+----- ----+----------------+
|结果_id |整数(11)|否 |优先原则 |空 |自动增量 |
|社区 ID |整数(11)|否 | |空 | |
|玩家1_id |整数(11)|否 | |空 | |
|玩家1_姓名 | varchar(50) | varchar(50)否 | |空 | |
|玩家1_团队| varchar(50) | varchar(50)否 | |空 | |
|玩家1_目标 |整数(11)|否 | |空 | |
|玩家1_结果 | varchar(3) | varchar(3) |否 | |空 | |
|玩家2_目标|整数(11)|否 | |空 | |
|玩家2_结果| varchar(3) | varchar(3) |否 | |空 | |
|玩家2_id |整数(11)|否 | |空 | |
|玩家2_名称 | varchar(50) | varchar(50)否 | |空 | |
|玩家2_团队| varchar(50) | varchar(50)否 | |空 | |
|玩家 1_pts |整数(11)|否 | |空 | |
|玩家2_pts |整数(11)|否 | |空 | |
|日期 |日期 |否 | |空 | |
+----------------+-------------+------+-----+----- ----+----------------+

我想运行一个 MySQL 查询来告诉我哪个球员进球最多。

该玩家可以是玩家 1 或玩家 2。

我想象它是这样的:

select member, goals, count(*) Total
from 
(
select player2_name as member, player1_goals as goals
from results
WHERE player1_id = 2
union all
select player1_name as member, player2_goals as goals
from results
WHERE player2_id = 2 

) AS T

group by goals
order by Total desc
Limit 1

这给出了结果:

member        | goals | Total
Jamie Charles | 2     | 11
Jamie Charles | 0     |  8
Jamie Charles | 3     |  5
Jamie Charles | 1     |  4
Jamie Charles | 5     |  1

但它看起来像是在说已经发生了多少个进球。所以正确的结果是(2x11)+(3*5)+(1*4)+(5*1)

此查询的正确语法是什么？

Answer 1

您需要按成员分组，使用sum聚合函数获得所有目标的总和。您不需要订单

select member, sum(goals), count(*) Total_goal_records
from 
(
select player2_name as member, player1_goals as goals
from results
WHERE player1_id = 2
union all
select player1_name as member, player2_goals as goals
from results
WHERE player2_id = 2 

) AS T

group by member 
order by Total desc;