查询在高尔夫数据库中查找低于标准杆的连续次数最多的T-SQL查询

Question

假设我们有一个高尔夫球场的SQL Server数据库。我们有一个名为Results的表，它有以下列(都是INT NOT NULL):

• PlayerId
• RoundId
• HoleId
• Score

我们还有一个名为Holes的表，它有以下列(所有INT NOT NULL):

• HoleId
• Number(1 ~ 18)
• Par(3,4或5)

我们要编写一个查询，查找谁在单轮中连续得分低于标准杆(一杆进洞、小鸟、鹰、双鹰)最多。查询应该返回以下信息:

• PlayerId
• RoundId
• NumberOfConsecutiveUnderParScores(anINT)
• UnderParHoleNumbers(VARCHAR包含逗号分隔值，按升序排列)

我想不出任何有用的东西。也许使用递归公共表表达式是可行的方法?

Answer 1

这是一个差距和岛屿问题，其中岛屿是给定球员和回合的一系列低于标准杆的洞得分。

我们可以用窗口和来识别岛屿，然后进行聚合和排序。

select playerId, roundId,
count(*) NumberOfConsecutiveUnderParHoles,
string_agg(number, ', ') within group (order by number) UnderParHoleNumbers
from (
select r.*, h.number,
sum(case when r.score < h.par then 0 else 1 end) over(partition by r.playerId, r.roundId order by h.number) grp
from results r
inner join holes h on h.holeId = r.holeId
) t
group by playerId, roundId, grp
order by count(*) desc

如果你只是想要最长的低于标准线，你可以把选择转到select top (1) with ties。