从表中选择参与者将导致
Participants-------JohnNancyJemAlbertSmithDaniella
Participants
-------
John
Nancy
Jem
Albert
Smith
Daniella
我想要一个查询结果显示
Player Opponent------ --------John NancyJem AlbertSmith Daniella
Player Opponent
------ --------
John Nancy
Jem Albert
Smith Daniella
这个问题的一个很好的例子是用于生成单淘汰锦标赛对的查询。
1rhkuytd1#
你可以使用窗口功能。只需引导+行号获得对手，然后获得奇数玩家
SELECT player,Opponent FROM ( SELECT player, Number, LEAD(player)OVER(ORDER BY Number)Opponent from ( SELECT player, ROW_NUMBER()OVER (ORDER BY player)Number FROM Table )X )Y WHERE Number % 2 <> 0
SELECT player,Opponent FROM
(
SELECT player, Number, LEAD(player)OVER(ORDER BY Number)Opponent from
SELECT player, ROW_NUMBER()OVER (ORDER BY player)Number FROM Table
)X
)Y
WHERE Number % 2 <> 0
wn9m85ua2#
你可以用 row_number() 和聚合：
row_number()
select min(player) as player, max(player) as opponentfrom (select t.*, row_number() over (order by player) as seqnum from t ) tgroup by floor( (seqnum - 1) / 2)
select min(player) as player, max(player) as opponent
from (select t.*, row_number() over (order by player) as seqnum
from t
) t
group by floor( (seqnum - 1) / 2)
oxosxuxt3#
非常感谢奥尔加和戈登的贡献。我发现奥尔加的答案更接近我的需要。再次感谢大家
3条答案
按热度按时间1rhkuytd1#
你可以使用窗口功能。只需引导+行号获得对手，然后获得奇数玩家
wn9m85ua2#
你可以用
row_number()和聚合：oxosxuxt3#
非常感谢奥尔加和戈登的贡献。我发现奥尔加的答案更接近我的需要。
再次感谢大家