mysql - 使用计数后的 SQL 连接行

Question

我正在尝试显示第 1 队的球员列表以及与他们一起使用的统计数据，这是表格

表 1:玩家

╔═══════════════════════════╗
║ id | fname | lname | team ║
╠═══════════════════════════╣
║ 1 | Jason | McFee | 1     ║
║ 2 | John | Smith  | 1     ║
║ 3 | Jack | Doe    | 1     ║
║ 4 | Wayne | Gretzky | 2   ║
╚═══════════════════════════╝

---

表 2:事件_目标

╔═════════════════════════╗
║ id  g_id  a1_id  a2_id  ║
╠═════════════════════════╣
║ 1   1  2  3             ║
║ 2   3  1  2             ║
║ 3   2  1  NULL          ║
╚═════════════════════════╝

我想得到的是这个
名称 - 从表中拼接
目标 - 玩家 ID 在 g_id 列中的所有次数
助攻 - COUNT (a1_id) + COUNT (a2_id) 所有时间 playerid 都在其中任一列中
POINTS - 进球数 + 助攻数

╔══════════════════════════════════════╗
║ id | name | goals | assists | points ║
╠══════════════════════════════════════╣
║ 1   J.McFee    1      2          3   ║
║ 2   J.Smith    1      2          3   ║
║ 3   J.Doe      1      1          2   ║
╚══════════════════════════════════════╝

我尝试过的事情

>SELECT id,
>CONCAT_WS(', 'SUBSTR(fname, 1, 1), lname) name,
>FROM players 
>WHERE teamid = 1

这让我返回了团队中 ID 为 1 的所有球员的名字，这些名字格式正确没问题。

我可以通过使用获取单个玩家的计数

>SELECT COUNT(g_id) FROM events_goals WHERE id = (playerid)

这会为玩家返回正确的进球数

但是当我把它们放在一起时，统计数据是错误的，当我知道应该有三行时它只显示 1 行

> SELECT a.id,
> CONCAT_WS(', 'SUBSTR(a.fname, 1, 1), a.lname) name,
> (COUNT(b.g_id))goals, 
> (COUNT(c.a1_id))a1, 
> (COUNT(d.a2_id))a2 
> FROM players a
> LEFT JOIN events_goals b ON a.id = b.g_id 
> LEFT JOIN events_goals c ON a.id = c.a1_id 
> LEFT JOIN events_goals d ON a.id = d.a2_id WHERE teamid = 1

Answer 1

这是您要查找的查询:

SELECT
  p.id,
  CONCAT_WS(', ', SUBSTR(p.fname, 1, 1), p.lname) name,
  COALESCE(eg_goals.goals, 0) goals, 
  COALESCE(eg_assists1.assists, 0) + COALESCE(eg_assists2.assists, 0) assists,
  COALESCE(eg_goals.goals, 0) + COALESCE(eg_assists1.assists, 0) + COALESCE(eg_assists2.assists, 0) points
FROM players p
LEFT JOIN (
  SELECT g_id, COUNT(g_id) goals FROM events_goals
  GROUP BY g_id
) eg_goals ON p.id = eg_goals.g_id
LEFT JOIN (
  SELECT a1_id, COUNT(a1_id) assists FROM events_goals
  GROUP BY a1_id
) eg_assists1 ON p.id = eg_assists1.a1_id
LEFT JOIN (
  SELECT a2_id, COUNT(a2_id) assists FROM events_goals
  GROUP BY a2_id
) eg_assists2 ON p.id = eg_assists2.a2_id
WHERE p.team = 1

您应该认真地重新考虑重新设计您的模式。将这些“事件”混合在同一个表中会导致可怕且难以维护的查询。