sql - 如何组织联合中的行

Question

我的结果有点问题，我试图为每支球队创建一个赛程表，无论是主场还是客场。我几乎让它工作了，但我遇到的问题显示在以下结果中:



我有两支球队在第一周的主场和客场比赛，同样的两支球队在一周的主场和客场比赛。

当我插入数据并读取它时，这应该显示的是第 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 天

输出没有显示出来，但如果每支球队都可以在一周内打主场比赛，然后在下周进行客场比赛，然后主场，然后客场等。 但是如果我们可以先显示上述输出，然后再摆弄命令他们回家和离开然后那很好。

Answer 1

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 |
+------------+------------+----------+------------+-----------+