使用列的最小值/最大值创建SQL视图

Question

我有一个表，其中存储了游戏ID列表、玩过该游戏的用户的用户ID以及每个用户在游戏中获得的分数，如下所示：

GameID     UserID     Score
1          Bob        12
1          Sally      14
2          Bob        17
2          Jane       17
3          Sally      16
3          Jane       10
3          Trish      10

我正试图编写一个SQL查询来创建一个数据视图，该视图将根据分数告诉我每个用户赢了、平了和输了多少场比赛。因此，视图如下所示：

UserId     NumWins     NumLosses     NumDraws
Bob        0           1             1
Sally      2           0             0
Jane       0           1             1
Trish      0           1             0

我正试图想出语法来创建一个以这种方式转换数据的视图，但遇到了麻烦。

Answer 1

您可以使用RANK()窗口函数和条件聚合：

with
ranks as (
select *, rank() over (partition by gameid order by score desc) rnk
from tablename
),
cte as (
select *, count(*) over (partition by gameid, rnk) counter
from ranks
)
select userid,
sum(case when rnk = 1 and counter = 1 then 1 else 0 end) NumWins,          
sum(case when rnk > 1 then 1 else 0 end) NumLosses,
sum(case when rnk = 1 and counter > 1 then 1 else 0 end) NumDraws
from cte  
group by userid

请参阅演示
结果：

> userid | NumWins | NumLosses | NumDraws
> :----- | ------: | --------: | -------:
> Bob    |       0 |         1 |        1
> Jane   |       0 |         1 |        1
> Sally  |       2 |         0 |        0
> Trish  |       0 |         1 |        0

Answer 2

您可以使用条件聚合作为以下

SELECT * INTO Games
FROM
(
VALUES
(1, 'Bob',     12),
(1, 'Sally',     14),
(2, 'Bob',     17),
(2, 'Jane',     17),
(3, 'Sally',     16),
(3, 'Jane',     10),
(3, 'Trish',     10)
) T(GameId, UserId, Score);
SELECT G.UserId,
SUM(CASE WHEN MXS = Score AND MNS <> Score THEN 1 ELSE 0 END) Wins,
SUM(CASE WHEN MXS > Score AND Score = MNS THEN 1 ELSE 0 END) Loses,
SUM(CASE WHEN MXS = MNS THEN 1 ELSE 0 END) Draws
FROM Games G
JOIN 
(
SELECT GameId, MAX(Score) MXS, MIN(Score) MNS
FROM Games
GROUP BY GameId
) T(GameId, MXS, MNS) 
ON G.GameId = T.GameId
GROUP BY UserId

这是一个数据库<>小提琴