创建夹具列表逻辑

Question

我需要一些关于尝试为足球赛季创建赛程表的逻辑帮助。我让球队自动设置，每支球队都需要在主场和客场互相比赛。我尝试了一个在网上看到的非常复杂的逻辑并试图操纵它，但我不断收到空的结果，所以我正在考虑废弃逻辑并重新开始。

以下是我的"团队"表：

TeamID  TeamAbbreviation   TeamName             LeagueID CountryID
1       MNU                Manchester United    1        1
2       CHE                Chelsea              1        1
3       LIV                Liverpool            1        1
4       ARS                Arsenal              1        1

下面是赛程表设置，目前我想填充赛程周（比赛轮次）和球队相互比赛。

[FixtureID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
[WeekNumber] INT NOT NULL, 
[HomeTeamID] INT CONSTRAINT FK_Fixture_HomeTeam FOREIGN KEY REFERENCES [Team](TeamID),
[AwayTeamID] INT CONSTRAINT FK_Fixture_AwayTeam FOREIGN KEY REFERENCES [Team](TeamID), 
[LeagueID] INT CONSTRAINT FK_Fixture_League FOREIGN KEY REFERENCES League(LeagueID)

以下是我尝试但显示空表失败的逻辑：

CREATE PROCEDURE [dbo].[Fixture_Insert]
    @HomeTeamID INT,
    @AwayTeamID INT,
    @FixtureDate DATE,
        @FixtureTime TIME,
        @LeagueID INT,
        @SeasonID INT
    AS
    SET NOCOUNT ON
    BEGIN
    DECLARE @Rounds INT
    SELECT @Rounds = count(*) 
    FROM dbo.Team
        IF @Rounds % 2 = 0 
        SET @Rounds = @Rounds - 1
    DECLARE @Matches INT
    SET @Matches = (@Rounds + 1)/2
    DECLARE @Match INT
    DECLARE @Max_Team INT
    SELECT @Max_Team = count(*) 
    FROM dbo.Team
    DECLARE @BotLeft INT
    DECLARE @BotRight INT
    DECLARE @TopRight INT
    --Truncate Table Fixture
    DECLARE @Round INT
    SET @Round = 1
    WHILE @Round <=  @Rounds
    BEGIN
          SET @Match = 1
          WHILE @Match <= @Matches
          BEGIN
                SET @BotLeft = @Rounds/2.0 + 1.25 + @Match - @Round / 2.0
                SET @BotRight = @Rounds/2.0 + @Rounds + 2.75 - @Match - @Round / 2.0
                SET @TopRight = @Rounds - @Match - @Round + 3.0
                IF @topRight > @match
                BEGIN
                      SET @HomeTeamID = @Match
                      SET @AwayTeamID = @TopRight
                END
                ELSE
                BEGIN
                      IF @BotRight = @Rounds + 1 AND @BotLeft = @Match * 2 - 1
                      BEGIN
                            SET @HomeTeamID = @Match
                            SET @AwayTeamID = @Rounds + 1
                      END
                      ELSE
                      BEGIN
                      IF @BotLeft = @BotRight
                            BEGIN
                                  SET @HomeTeamID = @BotLeft
                                  SET @AwayTeamID = @Rounds + 1
                            END
                            ELSE
                            BEGIN
                                  SET @HomeTeamID = @BotLeft
                                  SET @AwayTeamID = @BotRight
                            END
                      END
                END
                IF @AwayTeamID <= @Max_Team and @HomeTeamID <> @AwayTeamID
                INSERT INTO dbo.Fixture (WeekNumber, FixtureDate, FixtureTime, HomeTeamID, HomeScore, AwayTeamID, AwayScore, HomePoints, AwayPoints, LeagueID, SeasonID)
                VALUES (@Round, '10-10-2016', '12:00:00', @HomeTeamID, 2, @AwayTeamID, 1, 3, 0, 1, 1)
                SET @Match = @Match + 1
          END
          SET @Round = @Round + 1
    END
    --- insert home/away swaps
    INSERT INTO dbo.Fixture(WeekNumber, FixtureDate, FixtureTime, HomeTeamID, HomeScore, AwayTeamID, AwayScore, HomePoints, AwayPoints, LeagueID, SeasonID)
    SELECT @Round, '10-10-2016', '12:00:00', @HomeTeamID, 2, @AwayTeamID, 1, 3, 0, 1, 1 
    FROM dbo.Fixture
    END

Answer 1

你已经使这种方式比需要的更复杂 - 你只是想在teamID不匹配的地方CROSS JOIN你的团队表（即，一个团队不能自己玩）。

简化示例：

WITH team (teamId, teamName)
AS
(
    SELECT 1,'Manchester United'
    UNION SELECT 2,'Chelsea'
    UNION SELECT 3,'Liverpool'
    UNION SELECT 4,'Arsenal'
)
SELECT
    a.teamId as homeTeamId,
    a.teamName as homeTeam,
    b.teamId as awayTeamId,
    b.teamName as awayTeam
FROM team a
CROSS JOIN team b
WHERE a.teamId != b.teamId

请注意，目前还不清楚您如何计算weekNumber但我建议您查看排名函数