Ответ 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;