如何编写某种数学算法

另一个Oracle解决方案。

设置 ：

CREATE TABLE League (  LeagueID   INT PRIMARY KEY,   LeagueName VARCHAr(30) UNIQUE);CREATE TABLE Team (  TeamIDINT PRIMARY KEY,   TeamAbbreviation CHAr(3) UNIQUE,   TeamName         VARCHAr(50) UNIQUE,   LeagueID         INT ConSTRAINT FK_Team_League REFERENCES League(LeagueID) );CREATE TABLE Fixture (  FixtureID   INT PRIMARY KEY,  WeekNumber  INT NOT NULL,  FixtureDate DATE NULL,  HomeTeamID  INT NULL,  AwayTeamID  INT NULL,  LeagueID    INT ConSTRAINT FK_Fixture_League REFERENCES League(LeagueID));INSERT INTO League VALUES ( 1, 'League 1' );INSERT INTO League VALUES ( 2, 'League 2' );INSERT INTO Team VALUES ( 1, 'AAA', 'Team A', 1 );INSERT INTO Team VALUES ( 2, 'BBB', 'Team B', 1 );INSERT INTO Team VALUES ( 3, 'CCC', 'Team C', 1 );INSERT INTO Team VALUES ( 4, 'DDD', 'Team D', 1 );INSERT INTO Team VALUES ( 5, 'EEE', 'Team E', 2 );INSERT INTO Team VALUES ( 6, 'FFF', 'Team F', 2 );INSERT INTO Team VALUES ( 7, 'GGG', 'Team G', 2 );INSERT INTO Team VALUES ( 8, 'HHH', 'Team H', 2 );INSERT INTO Team VALUES ( 9, 'III', 'Team I', 2 );

插入物-灯具 ：

INSERT INTO FixtureWITH league_teams ( id, leagueid, idx, is_fake, num_teams, num_fake ) AS (  -- Generate a unique-per-league index for each team that is between 0  -- and the (number of teams - 1) and calculate the number of teams  -- and if this is an odd number then generate a fake team as well.  SELECt TeamID,         LeagueID,         ROW_NUMBER() OVER ( PARTITION BY LeagueID ORDER BY TeamID ) - 1,         0,         COUNT(1) OVER ( PARTITION BY LeagueID ),         MOD( COUNT(1) OVER ( PARTITION BY LeagueID ), 2 )  FROM Team  UNIOn ALL  SELECt NULL,         LeagueID,         COUNT(1),         1,         COUNT(1),         1  FROM   Team  GROUP BY LeagueID  HAVINg MOD( COUNT(1), 2 ) > 0),cte ( home_idx, away_idx, week_number, leagueID, num_teams, num_fake ) AS (  -- Start by calculating the round 1 games  SELECt idx,         num_teams + num_fake - 1 - idx,         1,         LeagueID,         num_teams,         num_fake  FROM   league_teams  WHERe  2 * idx < num_teamsUNIOn ALL  -- Then generate the successive rounds with the two cases when the  -- away team has the maximum index or otherwise.  SELECt CASE away_idxWHEN num_teams + num_fake - 1THEN home_idx + 1ELSE MOD( home_idx + 1, num_teams + num_fake -1 )END,         CASE away_idxWHEN num_teams + num_fake - 1THEN away_idxELSE MOD( away_idx + 1, num_teams + num_fake - 1 )END,        week_number + 1,        LeagueID,        num_teams,        num_fake  FROM  cte  WHERe week_number < num_teams + num_fake - 1)-- Finally join the cte results back to the League_Teams table to convert-- the indexes used in calculation back to the actual team ids.SELECt rn,       week_number,       NULL,       h.id,       a.id,       c.leagueidFROM   (         -- This step isn't necessary but it keeps the results in a nice order.         SELECt ROWNUM AS rn,     t.*         FROM   (-- Duplicate the results swapping home and away.SELECt week_number,       home_idx,       away_idx,       LeagueIdFROM   cteUNIOn ALLSELECt week_number + num_teams + num_fake - 1,       away_idx,       home_idx,       LeagueIdFROM   cte         ) t       ) c       INNER JOIN League_Teams h       ON ( c.home_idx = h.idx AND c.leagueId = h.leagueID )       INNER JOIN League_Teams a       ON ( c.away_idx = a.idx AND c.leagueId = a.leagueID )ORDER BY rn;

输出 ：

SELECt * FROM fixture; FIXTUREID WEEKNUMBER FIXTUREDATE         HOMETEAMID AWAYTEAMID   LEAGUEID---------- ---------- ------------------- ---------- ---------- ----------         1          1        1          4          1          2          1        2          3          1          3          1        5          2          4          1        6          9          2          5          1        7          8          2          6          2        2          4          1          7          2        3          1          1          8          2        6          2          9          2        7          5          2         10          2        8          9          2         11          3        3          4          1         12          3        1          2          1         13          3        7          2         14          3        8          6          2         15          3        9          5          2         16          4        8          2         17          4        9          7          2         18          4        5          6          2         19          5        9          2         20          5        5          8          2         21          5        6          7          2         22          4        4          1          1         23          4        3          2          1         24          6        5          2         25          6        9          6          2         26          6        8          7          2         27          5        4          2          1         28          5        1          3          1         29          7        6          2         30          7        5          7          2         31          7        9          8          2         32          6        4          3          1         33          6        2          1          1         34          8        7          2         35          8        6          8          2         36          8        5          9          2         37          9        8          2         38          9        7          9          2         39          9        6          5          2         40         10        9          2         41         10        8          5          2         42         10        7          6          2

（注意：这

FixtureDate

NULL