mysql - 如何使用 MySQL 以三列为一组检索最新的对？

Question

我正在研究冰球软件:试图找出您团队中谁在特定球员(在本例中 user_id = 1)上获得的分数最多。

数据结构:

goal_user_id | assist_user_id | second_assist_user_id
-----------------------------------
1            | 13856          | null
1            | 15157          | null
1            | 15157          | null
1            | 15157          | 18733
345          | 1              | 28703
18733        | 1              | null
36014        | 34867          | 1

期望的输出:

user_id | partner_id | total_points
-----------------------------------
1       | 15157      | 3
1       | 18733      | 2
1       | 13856      | 1
1       | 345        | 1
1       | 28703      | 1
1       | 34867      | 1
1       | 36014      | 1

SQL fiddle :

http://sqlfiddle.com/#!9/b1587/4/0 (注意:SQLFiddle 今天表现得很奇怪)。

我设法在两列上完成了它，但无法弄清楚三列:

SELECT 

  COUNT(goal_user_id) as total_points, 

  CASE WHEN goal_user_id <> 1 
    THEN goal_user_id
    ELSE assist_one_user_id
  END as partner_id,

  CASE WHEN goal_user_id < assist_one_user_id
    THEN CONCAT(goal_user_id,'-',assist_one_user_id) 
    ELSE CONCAT(assist_one_user_id,'-',goal_user_id) 
  END as player_pair 

FROM goals 

WHERE 
  assist_one_user_id IS NOT NULL AND (goals.goal_user_id = 1 OR goals.assist_one_user_id = 1)

GROUP BY player_pair

ORDER BY total_points DESC

Answer 1

我倾向于将所有行分成用户对。然后基于此进行聚合:

select u2, count(*) as total_points
from ((select goal_user_id as u1, assist_one_user_id as u2 from goals) union all
      (select assist_one_user_id, goal_user_id from goals) union all
      (select goal_user_id, assist_two_user_id from goals) union all
      (select assist_two_user_id, goal_user_id from goals) union all
      (select assist_one_user_id, assist_two_user_id from goals) union all
      (select assist_two_user_id, assist_one_user_id from goals)
     ) uu
where uu.u1 = 1 and uu.u2 is not null
group by u2
order by total_points desc;

Here是一个 SQL fiddle 。