Турнир по Round Robin для SQL Server

Я пытаюсь создать футбольный турнир с круговым ритмом, в котором каждая команда играет один раз (и только один раз) в неделю с лигой, состоящей из 12 команд.

Используя приведенный ниже код, вы можете увидеть, что у меня есть 12 команд. Я использовал CTE для создания светильников macth, чтобы каждая команда играла друг с другом один раз дома и один раз (это результат в 132 матчах).

Поскольку в общей сложности 132 матча приходится 12 команд, должно быть 6 матчей каждую неделю в течение 22 недель.

Как я буду генерировать недели, в которых происходит каждое приспособление?

DECLARE @Temp TABLE(
    TeamName VARCHAR(100),
    MatchWeek INT
)

INSERT INTO @Temp(TeamName) VALUES
('Team1'),
('Team2'),
('Team3'),
('Team4'),
('Team5'),
('Team6'),
('Team7'),
('Team8'),
('Team9'),
('Team10'),
('Team11'),
('Team12');

SELECT t1.Teamname, t2.Teamname
FROM @Temp t1, @Temp t2
WHERE t1.TeamName <> t2.TeamName

Ответ 1

Это сделает это. Демо

Как объясняется в в записи Википедии, алгоритм был взят из, он использует следующие позиции.

Последняя команда (лексикографическое упорядочение) будет зафиксирована в зеленом положении. Все остальные команды будут вращаться вокруг позиций 1-11. Светильники чередуются между использованием элементов Top и Bottom каждой пары в качестве команды Home или Away, чтобы избежать длинных последовательностей для команд.

В случае, если будет представлено нечетное количество команд, одна команда не будет иметь соответствия каждую неделю. Это описано ниже, создав в этом случае пустую нулевую строку, а затем используя тот же алгоритм.

WITH Teams
     AS (SELECT TeamName,
                TeamNum = ROW_NUMBER() OVER (ORDER BY TeamName),
                TeamCount = COUNT(*) OVER()
         FROM   @Temp
         /*Purpose of below is to add an extra dummy team if odd number 
           of teams. This null team name will be matched up against competitors 
           having no game that week */
         GROUP  BY TeamName WITH ROLLUP
         HAVING GROUPING(TeamName) = 0
                 OR COUNT(*) %2 = 1),
     Weeks
     AS ( /*We need numbers 1- 11 for a 12 team league etc. 
           Can use the row numbers calculated above for this*/
         SELECT TeamNum AS Week
         FROM   Teams
         WHERE  TeamNum < TeamCount),
     Positioned
     AS (SELECT TeamName,
                TeamNum,
                Week,
                position,
                TeamCount
         FROM   Teams
                CROSS JOIN Weeks
                /*Uses scheduling algorithm from Wikipedia with the last team in fixed position
                  and all other teams rotating around (between positions 1 and 11 in 12 team example)*/
                CROSS APPLY (SELECT CASE
                                      WHEN TeamNum = TeamCount THEN TeamCount
                                      ELSE 1 + ( ( TeamNum + Week - 1 ) % ( TeamCount - 1 ) )
                                    END) CA(position))
SELECT V.*
FROM   Positioned P1
       JOIN Positioned P2
         ON P1.Week = P2.Week
            /*Sum of positions should add up to TeamCount + 1*/
            AND P1.position = 1 + P2.TeamCount - P2.position
            /*Choose Home and Away from alternating Top and Bottom of pair to 
              avoid long runs of either for a team*/
            AND (P2.Week %2 = 0 AND P1.position < P2.position
                 OR P2.Week %2 = 1 AND P1.position > P2.position)
            /*For second half of the season just reversing the "Home" and "Away" teams */
       CROSS APPLY ( VALUES(P1.TeamName, P2.TeamName, P1.Week),
                           (P2.TeamName, P1.TeamName, P1.Week + P1.TeamCount - 1) ) V(HomeTeam, AwayTeam, Week)
/*Exclude any dummy matches if odd number of teams*/
WHERE  V.AwayTeam IS NOT NULL
       AND V.HomeTeam IS NOT NULL
ORDER  BY V.Week

В качестве альтернативы, самосоединение Positioned можно избежать с некоторой агрегацией, заменив последнюю часть указанного выше запроса. демо

     Positioned
     AS (SELECT TeamName,
                TeamNum,
                Week,
                position,
                TeamCount,
                /*Sum of opposing positions should add up to TeamCount + 1 so can calculate slot for grouping*/
                Slot = CASE WHEN position <= TeamCount / 2 THEN position ELSE TeamCount + 1 - position END
         FROM   Teams
                CROSS JOIN Weeks
                /*Uses scheduling algorithm from Wikipedia with the last team in fixed position
                  and all other teams rotating around (between positions 1 and 11 in 12 team example)*/
                CROSS APPLY (SELECT CASE
                                      WHEN TeamNum = TeamCount
                                        THEN TeamCount
                                      ELSE 1 + ( ( TeamNum + Week ) % ( TeamCount - 1 ) )
                                    END) CA(position)),
     Matches
     AS (SELECT Week,
                Slot,
                TeamCount,
                TopTeam = MAX(CASE WHEN position = slot THEN TeamName END),
                BottomTeam = MAX(CASE WHEN position <> slot THEN TeamName END)
         FROM   Positioned
         GROUP  BY Week,
                   Slot,
                   TeamCount)
SELECT CA.*
FROM   Matches
       CROSS APPLY (
                   /*Choose Home and Away from alternating Top and Bottom of pair to 
                     avoid long runs of either for a team*/
                   /*First two rows are for alternate weeks in the 1st half of the season */
                   SELECT TopTeam, BottomTeam, Week
                   WHERE  Week %2 = 0
                   UNION ALL
                   SELECT BottomTeam, TopTeam, Week
                   WHERE  Week %2 > 0
                   UNION ALL
                   /*For second half of the season just reversing the "Home" and "Away" teams */
                   SELECT BottomTeam, TopTeam, Week + TeamCount - 1
                   WHERE  Week %2 = 0
                   UNION ALL
                   SELECT TopTeam, BottomTeam, Week + TeamCount - 1
                   WHERE  Week %2 > 0) CA(HomeTeam, AwayTeam, Week)
/*Exclude any dummy matches if odd number of teams*/
WHERE  CA.AwayTeam IS NOT NULL
       AND CA.HomeTeam IS NOT NULL
ORDER  BY CA.Week;

Ответ 2

Итак, здесь теория: https://nrich.maths.org/1443

Для реализации в SQL сначала нужен список команд

DECLARE @teams TABLE(
    TeamId int identity(0,1),
    TeamName VARCHAR(100)    
)
INSERT @teams(TeamName) VALUES
('Team01'),
('Team02'),
('Team03'),
('Team04'),
('Team05'),
('Team06'),
('Team07'),
('Team08'),
('Team09'),
('Team10'),
('Team11'),
('Team12');

Теперь мы создаем круглые наборы крепежных приспособлений. Я уверен, что смогу сделать это, используя объекты геометрии и повернув их, но...

declare @roundRobin table (fixtureId int, week int, homeTeam int, awayTeam int)
insert @roundRobin 
      select 1, 1, 0, 1
union select 2, 1, 2, 3
union select 3, 1, 4, 5
union select 4, 1, 6, 7
union select 5, 1, 8, 9
union select 6, 1, 10, 11
declare @week int = 2
while @week <= 11
begin
    insert @roundRobin 
    select 1, @week, 0, awayTeam from @roundRobin where week = @week - 1 and fixtureId=2
    union all
    select 2, @week,(select awayTeam from @roundRobin where week = @week - 1 and fixtureId=1), (select awayTeam from @roundRobin where week = @week - 1 and fixtureId=3) 
    union all
    select 3, @week,(select homeTeam from @roundRobin where week = @week - 1 and fixtureId=2), (select awayTeam from @roundRobin where week = @week - 1 and fixtureId=4) 
    union all
    select 4, @week,(select homeTeam from @roundRobin where week = @week - 1 and fixtureId=3), (select awayTeam from @roundRobin where week = @week - 1 and fixtureId=5) 
    union all
    select 5, @week,(select homeTeam from @roundRobin where week = @week - 1 and fixtureId=4), (select awayTeam from @roundRobin where week = @week - 1 and fixtureId=6) 
    union all
    select 6,@week,(select homeTeam from @roundRobin where week = @week - 1 and fixtureId=5), (select homeTeam from @roundRobin where week = @week - 1 and fixtureId=6) 
    select @week = @week + 1
end

Теперь создайте набор этих светильников с измененной командой дома и гостей.

insert @roundRobin  
select fixtureId, week+11, awayTeam, homeTeam from @roundRobin

Создайте список недель в случайном порядке, чтобы остановить прогоны домашних/выездных светильников

declare @weeks table (Week int, WeekOrder int)
insert @weeks 
select number, row_number() over (order by randomorder)
from
(   select number, newid() randomorder from master..spt_values where type='p' and number between 1 and 22 ) v
order by number

И теперь, каждую неделю светильники. Тада!

select 
    weekorder,
    ht.TeamName homeTeam,
    at.TeamName awayTeam
from @weeks w
    inner join @roundRobin rr on w.Week = rr.week
    inner join @teams ht on rr.homeTeam = ht.TeamId
    inner join @teams at on rr.awayTeam = at.TeamId
order by weekorder, hometeam

Теперь у меня появилось новое уважение к компьютеру с лучшей лигой.