分组每行几个聚集体

Question

我有一个餐桌游戏，其中我的IDS是player_1，player_2和winner

player_1   player_2   winner
1          2          1
1          2          1
1          3          3
2          3          2
2          1          2

我想进行查询，其中最终结果与以下内容相似：

players     num_games_played   wins  
1|2         3                  2|1
1|3         1                  0|1
2|3         1                  1|0

基本上，我需要按结果进行分组，以显示玩游戏的列表和每个玩家的胜利。

我不确定我是否应该坚持这种表格或制作一些关系表。

关于最佳方法的任何建议？

Answer 1

这是您可以解决这个问题的一种方法。

if OBJECT_ID('tempdb..#Games') is not null
    drop table #Games
create table #Games
(   
    player1 int
    , player2 int
    , winner int
)
insert #Games values
(1, 2, 1)
, (1, 2, 1)
, (1, 3, 3)
, (2, 3, 2)
, (2, 1, 2)
select players = convert(varchar(5), player1) + '|' + convert(varchar(5), player2)
    , num_games_played = count(*)
    , convert(varchar(5), sum(case when winner = player1 then 1 else 0 end)) + '|' + convert(varchar(5), sum(case when winner = player2 then 1 else 0 end))
from
(
    select player1
        , player2
        , winner
    from #Games
    where player1 < player2
    union all
    select player2
        , player1
        , winner
    from #Games
    where player2 < player1
) x
group by convert(varchar(5), player1) + '|' + convert(varchar(5), player2)

Answer 2

我会使用一些关系表，您很容易获得结果，并且如果您稍后进行一些更新，可以从两个以上的玩家那里提供帮助。也许有一个带有游戏/比赛信息的桌子，另一个桌子链接到游戏表，您的玩家在其中玩了比赛，并为结果(获胜者(进行了一些字段。

Answer 3

这是另一种代码，它使用SQL查询的类似想法

with demo_solution as (
select 
p1=case when player1>player2 then player2 else player1 end,
p2=case when player1>player2 then player1 else player2 end,
winner
from Games
)
select 
players = cast(p1 as varchar)+'|'+cast(p2 as varchar),
num_games_played = count(1),
wins = cast(sum(case when winner=p1 then 1 else 0 end) as varchar)
     +'|'+
    cast(sum(case when winner=p2 then 1 else 0 end) as varchar)
from demo_solution
group by cast(p1 as varchar)+'|'+cast(p2 as varchar)