如何使用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;

这里是一个SQL。