我的结果有点问题，我试图为每支球队创建一个赛程表，无论是主场还是客场。我几乎让它工作了，但我遇到的问题显示在以下结果中:
我有两支球队在第一周的主场和客场比赛，同样的两支球队在一周的主场和客场比赛。
当我插入数据并读取它时，这应该显示的是第 1 周，第 4 队在主场与 18 队比赛，然后在第 4 周进行相反的比赛。
换句话说，第 2 行和第 3 行不正确，应该只显示第 1 行和第 4 行。我需要在下面的代码中更改什么才能使其正常工作？
INSERT INTO dbo.Fixture (WeekNumber, HomeTeamID, AwayTeamID, FixtureDate, LeagueID)
SELECT
ROW_NUMBER() OVER (PARTITION BY h.teamID ORDER BY h.TeamID, a.TeamID, h.LeagueID) AS WeekNumber,
h.TeamID,
a.TeamID,
DATEADD(day,(ROW_NUMBER() OVER (ORDER BY h.LeagueID)-1)*7,@StartFixtureWeek) AS FixtureWeek,
h.LeagueID
FROM dbo.Team h
CROSS JOIN dbo.Team a
WHERE h.TeamID <> a.TeamID
AND h.LeagueID = a.LeagueID
UNION
SELECT
ROW_NUMBER() OVER (PARTITION BY a.teamID ORDER BY h.TeamID, a.TeamID, h.LeagueID) AS WeekNumber,
h.TeamID,
a.TeamID,
DATEADD(day,(ROW_NUMBER() OVER (ORDER BY a.LeagueID)-1)*7,@StartFixtureWeek) AS FixtureWeek,
h.LeagueID
FROM dbo.Team h
CROSS JOIN dbo.Team a
WHERE h.TeamID <> a.TeamID
AND h.LeagueID = a.LeagueID
select * from dbo.Fixture
WHERE (HomeTeamID = 4 AND AwayTeamID = 18) OR (HomeTeamID = 18 AND AwayTeamID = 4)
更新:
以下是所需输出的解释和设计:
WeekNumber HomeTeamID AwayTeamID FixtureWeek LeagueID
1 1 4 10-06-2016 1
2 1 3 17-06-2016 1
3 1 2 24-06-2016 1
4 4 1 30-06-2016 1
5 3 1 06-07-2016 1
6 2 1 13-07-2016 1
1 2 3 10-06-2016 1
2 2 4 17-06-2016 1
3 3 4 24-06-2016 1
4 3 2 30-06-2016 1
5 4 2 06-07-2016 1
6 4 3 13-07-2016 1
1 5 8 10-06-2016 2
2 5 7 17-06-2016 2
3 5 6 24-06-2016 2
4 8 5 30-06-2016 2
5 7 5 06-07-2016 2
6 6 5 13-07-2016 2
1 6 7 10-06-2016 2
2 6 8 17-06-2016 2
3 7 8 24-06-2016 2
4 7 6 30-06-2016 2
5 8 6 06-07-2016 2
6 8 7 13-07-2016 2
好的，所以有两个联赛(LeagueID 1 和 LeagueID 2)
在联赛 1 中有 4 支球队(TeamID)——1、2、3、4——他们在主客场互相比赛，但他们不能在同一周内打两场比赛。
在联赛 2 中，有 4 支球队(TeamID)——5、6、7、8——他们在主客场互相比赛，但他们不能在同一周内打两场比赛。
两个联赛在同一天开始，每场比赛(或换句话说每周)增加 7 天
输出没有显示出来，但如果每支球队都可以在一周内打主场比赛，然后在下周进行客场比赛，然后主场，然后客场等。 但是如果我们可以先显示上述输出，然后再摆弄命令他们回家和离开然后那很好。
最佳答案
CROSS JOIN 和 h.TeamID <> a.TeamID 生成所有对，即 (1,2) 和 (2,1) 。我认为如果我们将它们分开会更容易，因此 WHERE 过滤器将变为 h.TeamID > a.TeamID 或 h.TeamID < a.TeamID 。
应为每个联赛分配周数，因此应按 LeagueID 对其进行分区。然后就是以某种方式对团队进行排序，然后以相反的方向排序。
在下面的结果中，您可以看到 6 周的团队配对模式如何在两个相反的方向重复。
最有可能使用一个使用 CROSS JOIN 而没有 <> 的 UNION 和一个更复杂的 ROW_NUMBER 表达式来实现相同或相似的结果。
旁注:如果您真的不需要，请不要使用 UNION。使用 UNION ALL 。
旁注 2:在这里使用 CROSS JOIN 或 INNER JOIN 都没有关系。优化器将生成相同的执行计划。我认为这里的 CROSS JOIN 更具可读性，更清楚地显示了生成笛卡尔积的意图 - 所有对的集合。
样本数据
DECLARE @T TABLE (TeamID int, LeagueID int);
INSERT INTO @T (TeamID, LeagueID) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 2),
(6, 2),
(7, 2),
(8, 2);
查询
SELECT
h.TeamID AS HomeTeamID
,a.TeamID AS AwayTeamID
,h.LeagueID
,ROW_NUMBER() OVER
(PARTITION BY h.LeagueID ORDER BY h.TeamID, a.TeamID) AS WeekNumber
,1 AS SortOrder
FROM
@T AS h
CROSS JOIN @T AS a
WHERE
h.LeagueID = a.LeagueID
AND h.TeamID < a.TeamID
UNION ALL
SELECT
h.TeamID AS HomeTeamID
,a.TeamID AS AwayTeamID
,h.LeagueID
,ROW_NUMBER() OVER
(PARTITION BY h.LeagueID ORDER BY a.TeamID DESC, h.TeamID DESC) AS WeekNumber
,2 AS SortOrder
FROM
@T AS h
CROSS JOIN @T AS a
WHERE
h.LeagueID = a.LeagueID
AND h.TeamID > a.TeamID
ORDER BY
LeagueID
,SortOrder
,WeekNumber
;
结果
+------------+------------+----------+------------+-----------+
| HomeTeamID | AwayTeamID | LeagueID | WeekNumber | SortOrder |
+------------+------------+----------+------------+-----------+
| 1 | 2 | 1 | 1 | 1 |
| 1 | 3 | 1 | 2 | 1 |
| 1 | 4 | 1 | 3 | 1 |
| 2 | 3 | 1 | 4 | 1 |
| 2 | 4 | 1 | 5 | 1 |
| 3 | 4 | 1 | 6 | 1 |
| 4 | 3 | 1 | 1 | 2 |
| 4 | 2 | 1 | 2 | 2 |
| 3 | 2 | 1 | 3 | 2 |
| 4 | 1 | 1 | 4 | 2 |
| 3 | 1 | 1 | 5 | 2 |
| 2 | 1 | 1 | 6 | 2 |
| 5 | 6 | 2 | 1 | 1 |
| 5 | 7 | 2 | 2 | 1 |
| 5 | 8 | 2 | 3 | 1 |
| 6 | 7 | 2 | 4 | 1 |
| 6 | 8 | 2 | 5 | 1 |
| 7 | 8 | 2 | 6 | 1 |
| 8 | 7 | 2 | 1 | 2 |
| 8 | 6 | 2 | 2 | 2 |
| 7 | 6 | 2 | 3 | 2 |
| 8 | 5 | 2 | 4 | 2 |
| 7 | 5 | 2 | 5 | 2 |
| 6 | 5 | 2 | 6 | 2 |
+------------+------------+----------+------------+-----------+
关于sql - 如何组织联合中的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37697576/