如何用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小提琴:

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;