如何通过case语句联合所有SQL-SQLite查询? [英] How to make union all SQL-SQLite query by case statement?

问题描述

我在用多列连接两个表时感到困惑.我想从每个组中找到得分最高的球员 ID.例如，玩家 45、30 和 65 分在第 1 组，而 45 是得分最高的获胜者.

I am confused while joining two tables with mulitple columns. I want to find the players IDs from each group who got the maximum score. For example, player 45, 30, and 65 are in group 1 and 45 is the winner with maximum score.

请帮忙...

这是玩家"表

player_id | group_id
  -----------+----------
   20        | 2
   30        | 1
   40        | 3
   45        | 1
   50        | 2
   65        | 1

表匹配"

match_id | first_player | second_player | first_score | second_score
 1          | 30           | 45            | 10          | 12
   2        | 20           | 50            | 5           | 5
   13       | 65           | 45            | 10          | 10
   5        | 30           | 65            | 3           | 15
   42       | 45           | 65            | 8           | 4

预期输出:

group_id | winner_id
  ----------+-----------
   1        | 45
   2        | 20
   3        | 40

我试过了:

select distinct gid, (case where...) as winner_id
from
(select a.first_player, a.second_player, a.first_score as fs, 
a.second_score as ss , p.group_id as gid
from
mathches as a
join players as p 
on p.player_id IN (a.first_player,a.second_player))
group by gid

推荐答案

首先你必须得到每个玩家的总分，然后加入players.
然后使用FIRST_VALUE()窗口函数获取每组的top player:

First you must get the total score of each player and then join to players.
Then use FIRST_VALUE() window function to get the top player of each group:

SELECT DISTINCT p.group_id, 
       FIRST_VALUE(p.player_id) OVER (PARTITION BY p.group_id ORDER BY m.score DESC) winner_id
FROM players p
LEFT JOIN (
  SELECT player, SUM(score) score
  FROM (
    SELECT match_id, first_player player, first_score score FROM matches
    UNION ALL
    SELECT match_id, second_player, second_score FROM matches
  ) t
  GROUP BY player
) m ON m.player = p.player_id  

请参阅演示.

这篇关于如何通过case语句联合所有SQL-SQLite查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！