我正在尝试从分数保持系统中获取一些个人统计信息。本质上,球队被安排参加比赛
Match
---------
Matchid (uniqueidentifier)
SessionId (int)
WeekNum (int)
这些比赛分为几组,其中团队中的两名特定球员互相比赛
MatchSet
-----------
SetId (int)
Matchid (uniqueidentifier)
HomePlayer (int)
AwayPlayer (int)
WinningPlayer (int)
LosingPlayer (int)
WinningPoints (int)
LosingPoints (int)
MatchEndTime (datetime)
为了允许球员缺席,每场比赛允许球员玩两次。每组的得分将计入他们的球队总数,但对于个人奖项,仅应计算球员第一次参加比赛。
我一直在尝试利用CTE对行进行编号
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY MatchId ORDER BY MatchEndTime) AS rn
FROM
(SELECT
SetId, MS.MatchId, WinningPlayer, LosingPlayer,
HomePlayer, AwayPlayer, WinningPoints, LosingPoints, MatchEndTime
FROM
MatchSet MS
INNER JOIN
[Match] M ON M.MatchId = MS.MatchId AND M.[Session] = @SessionId
)
但是我很努力,因为玩家可能是给定集合中的主场球员或客场球员(也可能是获胜者或失败者)
理想情况下,然后可以基于WinningPlayer或LosingPlayer将结果加入回到玩家表,这将使我获得个人排名的列表
我认为第一步是编写一些CTE,这些CTE会将数据放入一个结构中,在此结构中您可以评估玩家得分而不管赢/输。这是一个可能的开始:
;with PlayersPoints as
(
select m.MatchId
,m.SessionId
,m.WeekNum
,ms.SetId
,ms.WinningPlayer as PlayerId
,ms.WinningPoints as Points
,'W' as Outcome
,ms.MatchEndTime
from MatchSet ms
join Match m on on ms.MatchId = m.MatchId
and m.SessionId = @SessionId
union all
select m.MatchId
,m.SessionId
,m.WeekNum
,ms.SetId
,ms.LosingPlayer as PlayerId
,ms.LosingPoints as Points
,'L' as Outcome
,ms.MatchEndTime
from MatchSet ms
join Match m on on ms.MatchId = m.MatchId
and m.SessionId = @SessionId
)
, PlayerMatch as
(
select SetId
,WeekNum
,MatchId
,PlayerId
,row_number() over (partition by PlayerId, WeekNum order by MatchEndTime) as PlayerMatchSequence
from PlayerPoints
)
....
第一个CTE提取每个玩家的分数,第二个CTE识别匹配的人。因此,要计算单个点,您需要查找PlayerMatchSequence = 1。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句