Oracle 创建运动器材的过程无法遵守针对同一表的条件查询

嗨，汤姆，

我正在尝试从所有可能的对决列表 (all_possible_fixture-其中包含每支在主场与对手比赛的球队，而与之相反的是，AAA (主场) v BBB (客场) 和BBB (主场) v AAA (客场) 是明显有效的潜在匹配)，有几个条件。共有18支球队，在17轮比赛中互相比赛一次，每轮比赛9场。球队不能在同一轮比赛中出现两次，也不能在17轮比赛中主场或客场比赛超过9场。理想情况下，他们不能连续两次打主场或客场比赛。

我试图解决这个问题的方式是通过创建一个过程来循环通过轮次和匹配的数量，将与定义的条件对齐的匹配插入到该表中，然后使用该表然后定义将来的适当插入。

例如，LiveSQL中的语句3包含以下内容:

FOR ROUND_NO IN 1 。。 17 LOOP   
    FOR MATCH_ID IN 1 。。 9 LOOP   
      INSERT INTO FIXTURE(HOME_TEAM, AWAY_TEAM, ROUND)   
   
      SELECT HOME_TEAM, AWAY_TEAM, ROUND_NO AS ROUND FROM (   
        SELECT HOME_TEAM, AWAY_TEAM, DBMS_RANDOM。VALUE AS RND FROM ALL_POSSIBLE_FIXTURES   
        WHERE   
          -- this match cannot be in this round   
          (HOME_TEAM||AWAY_TEAM NOT IN (SELECT HOME_TEAM||AWAY_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (AWAY_TEAM||HOME_TEAM NOT IN (SELECT AWAY_TEAM||HOME_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          -- the teams cannot already be playing each other in this configuration   
          (HOME_TEAM||AWAY_TEAM NOT IN (SELECT HOME_TEAM||AWAY_TEAM FROM FIXTURE)) AND   
          (AWAY_TEAM||HOME_TEAM NOT IN (SELECT AWAY_TEAM||HOME_TEAM FROM FIXTURE)) AND   
          -- the teams cannot have had two home games in the last two rounds (***** NB。 This doesnt appear to be working *****)   
          (HOME_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE WHERE ROUND > ROUND_NO-2 GROUP BY HOME_TEAM HAVING COUNT(*) > 1)) AND   
          (AWAY_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE WHERE ROUND > ROUND_NO-2 GROUP BY AWAY_TEAM HAVING COUNT(*) > 1)) AND   
          -- these teams cannot be scheduled to play more than 11 games home or away   
          (HOME_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE GROUP BY HOME_TEAM HAVING COUNT(*) > 9)) AND   
          (AWAY_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE GROUP BY AWAY_TEAM HAVING COUNT(*) > 9)) AND   
          -- these teams cannot already be in this round, either home or away   
          (HOME_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (AWAY_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (HOME_TEAM NOT IN (SELECT AWAY_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO)) AND   
          (AWAY_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE WHERE ROUND = ROUND_NO))   
          ORDER BY 3   
      ) WHERE ROWNUM = 1;   
      COMMIT;   
    END LOOP;   
  END LOOP;  
复制

但是，尽管它工作得相当好，但我很难让它尊重家庭/外出顺序条件-

 (HOME_TEAM NOT IN (SELECT HOME_TEAM FROM FIXTURE WHERE ROUND > ROUND_NO-2 GROUP BY HOME_TEAM HAVING COUNT(*) > 1)) 
复制

从样本输出中可以看出 (这是随机的，再次运行时会改变)，其中team AAA is the home team in three successive rounds in rounds 2-4。

I would have thought that at least by round 4, this home team would appear as having a count > 1 in the last two rounds, and therefore not be a valid selection for the home team in a round 4 match。 But as you can see, that's not the case。

有人可以向我解释一下这是怎么回事吗？

非常感谢，
安德鲁

编辑: 根据要求-关于所有可能的固定装置表的更多信息

我逐行而不是insert-select，所以我们可以输出每个候选行。

这是我的编辑

SQL> CREATE TABLE ALL_POSSIBLE_FIXTURES AS
  2  WITH X(TEAM) AS (
  3  SELECT 'AAA' FROM DUAL UNION ALL
  4  SELECT 'BBB' FROM DUAL UNION ALL
  5  SELECT 'CCC' FROM DUAL UNION ALL
  6  SELECT 'DDD' FROM DUAL UNION ALL
  7  SELECT 'EEE' FROM DUAL UNION ALL
  8  SELECT 'FFF' FROM DUAL UNION ALL
  9  SELECT 'GGG' FROM DUAL UNION ALL
 10  SELECT 'HHH' FROM DUAL UNION ALL
 11  SELECT 'III' FROM DUAL UNION ALL
 12  SELECT 'JJJ' FROM DUAL UNION ALL
 13  SELECT 'KKK' FROM DUAL UNION ALL
 14  SELECT 'LLL' FROM DUAL UNION ALL
 15  SELECT 'MMM' FROM DUAL UNION ALL
 16  SELECT 'NNN' FROM DUAL UNION ALL
 17  SELECT 'OOO' FROM DUAL UNION ALL
 18  SELECT 'PPP' FROM DUAL UNION ALL
 19  SELECT 'QQQ' FROM DUAL UNION ALL
 20  SELECT 'RRR' FROM DUAL
 21  )
 22  SELECT
 23    X1.TEAM AS HOME_TEAM,
 24    X2.TEAM AS AWAY_TEAM
 25  FROM X X1
 26  CROSS JOIN X X2
 27  WHERE X1.TEAM <> X2.TEAM ;

Table created.

SQL>
SQL> CREATE TABLE FIXTURE
  2  ( "HOME_TEAM" VARCHAR2(15 BYTE),
  3    "AWAY_TEAM" VARCHAR2(15 BYTE),
  4    "ROUND" NUMBER,
  5    MATCH number
  6  );

Table created.

SQL> set serverout on
SQL> DECLARE
  2    v_cnt NUMBER;
  3
  4  PROCEDURE insertGames IS
  5    l_home varchar2(10);
  6    l_away varchar2(10);
  7    l_round int;
  8    l_match int;
  9  BEGIN
 10    delete FIXTURE;
 11    FOR iter_round IN 1 .. 17 LOOP
 12      FOR iter_match IN 1 .. 9 LOOP
 13        SELECT HOME_TEAM, AWAY_TEAM, iter_round , iter_match
 14        into l_home, l_away, l_round, l_match
 15        FROM (
 16          select home_team, away_team, dbms_random.value as rnd from all_possible_fixtures a
 17          --
 18          -- the teams cannot already be playing each other in this configuration
 19          --
 20          where  not exists ( select null from fixture where home_team||away_team = a.home_team||a.away_team )
 21            and  not exists ( select null from fixture where away_team||home_team = a.away_team||a.home_team )
 22          --
 23          -- these teams cannot be scheduled to play more than 9 games home or away
 24          --
 25            and  ( select count(*) from fixture where home_team = a.home_team ) <= 8
 26            and  ( select count(*) from fixture where away_team = a.away_team ) <= 8
 27          --
 28          -- the teams cannot have had two home games in the last two rounds (***** NB. This doesnt appear to be working *****)
 29          --
 30            and  ( select count(*) from fixture where round > iter_round-2 and home_team = a.home_team ) < 2
 31          --
 32          -- these teams cannot already be in this round, either home or away
 33          --
 34            and not exists ( select null from fixture where round = iter_round and home_team = a.home_team)
 35            and not exists ( select null from fixture where round = iter_round and home_team = a.away_team)
 36            and not exists ( select null from fixture where round = iter_round and away_team = a.home_team)
 37            and not exists ( select null from fixture where round = iter_round and away_team = a.away_team)
 38          order by 3
 39        ) WHERE ROWNUM = 1;
 40
 41        dbms_output.put_line('Round: '||lpad(l_round,2)||'  Match:'||lpad(l_match,2)||'   '||l_home||','||l_away);
 42        INSERT INTO FIXTURE(HOME_TEAM, AWAY_TEAM, ROUND, MATCH)
 43        values (l_home, l_away, l_round,l_match );
 44
 45        COMMIT;
 46      END LOOP;
 47    END LOOP;
 48  END;
 49
 50  BEGIN
 51    for i in 1 .. 100
 52    loop
 53      begin
 54        insertGames;
 55        exit;
 56      exception
 57        when no_data_found then
 58          if i = 100 then raise; end if;
 59      end;
 60    end loop;
 61  END;
 62  /

[snip]

Round:  1  Match: 1   FFF,DDD
Round:  1  Match: 2   HHH,QQQ
Round:  1  Match: 3   III,CCC
Round:  1  Match: 4   BBB,PPP
Round:  1  Match: 5   JJJ,NNN
Round:  1  Match: 6   KKK,AAA
Round:  1  Match: 7   MMM,EEE
Round:  1  Match: 8   LLL,RRR
Round:  1  Match: 9   OOO,GGG
Round:  2  Match: 1   FFF,BBB
Round:  2  Match: 2   GGG,EEE
Round:  2  Match: 3   JJJ,OOO
Round:  2  Match: 4   DDD,MMM
Round:  2  Match: 5   HHH,III
Round:  2  Match: 6   RRR,QQQ
Round:  2  Match: 7   PPP,NNN
Round:  2  Match: 8   LLL,CCC
Round:  2  Match: 9   AAA,KKK
Round:  3  Match: 1   KKK,DDD
Round:  3  Match: 2   HHH,EEE
Round:  3  Match: 3   QQQ,PPP
Round:  3  Match: 4   OOO,FFF
Round:  3  Match: 5   RRR,III
Round:  3  Match: 6   BBB,CCC
Round:  3  Match: 7   GGG,AAA
Round:  3  Match: 8   NNN,MMM
Round:  3  Match: 9   LLL,JJJ
Round:  4  Match: 1   RRR,PPP
Round:  4  Match: 2   KKK,BBB
Round:  4  Match: 3   FFF,NNN
Round:  4  Match: 4   III,AAA
Round:  4  Match: 5   EEE,GGG
Round:  4  Match: 6   OOO,CCC
Round:  4  Match: 7   HHH,MMM
Round:  4  Match: 8   LLL,DDD
Round:  4  Match: 9   QQQ,JJJ
Round:  5  Match: 1   HHH,PPP
Round:  5  Match: 2   EEE,BBB
Round:  5  Match: 3   III,NNN
Round:  5  Match: 4   LLL,KKK
Round:  5  Match: 5   QQQ,AAA
Round:  5  Match: 6   CCC,MMM
Round:  5  Match: 7   GGG,FFF
Round:  5  Match: 8   OOO,DDD
Round:  5  Match: 9   JJJ,RRR
Round:  6  Match: 1   QQQ,GGG
Round:  6  Match: 2   CCC,KKK
Round:  6  Match: 3   OOO,RRR
Round:  6  Match: 4   JJJ,LLL
Round:  6  Match: 5   PPP,BBB
Round:  6  Match: 6   DDD,EEE
Round:  6  Match: 7   NNN,HHH
Round:  6  Match: 8   AAA,III
Round:  6  Match: 9   FFF,MMM
Round:  7  Match: 1   RRR,FFF
Round:  7  Match: 2   KKK,GGG
Round:  7  Match: 3   JJJ,HHH
Round:  7  Match: 4   MMM,PPP
Round:  7  Match: 5   NNN,III
Round:  7  Match: 6   BBB,DDD
Round:  7  Match: 7   CCC,OOO
Round:  7  Match: 8   EEE,QQQ
Round:  7  Match: 9   LLL,AAA
Round:  8  Match: 1   OOO,LLL
Round:  8  Match: 2   PPP,RRR
Round:  8  Match: 3   KKK,FFF
Round:  8  Match: 4   BBB,JJJ
Round:  8  Match: 5   GGG,HHH
Round:  8  Match: 6   EEE,NNN
Round:  8  Match: 7   MMM,QQQ
Round:  8  Match: 8   CCC,AAA
Round:  8  Match: 9   III,DDD
Round:  9  Match: 1   HHH,RRR
Round:  9  Match: 2   III,BBB
Round:  9  Match: 3   GGG,QQQ
Round:  9  Match: 4   EEE,MMM
Round:  9  Match: 5   CCC,PPP
Round:  9  Match: 6   FFF,JJJ
Round:  9  Match: 7   DDD,NNN
Round:  9  Match: 8   KKK,LLL
Round:  9  Match: 9   AAA,OOO
Round: 10  Match: 1   AAA,CCC
Round: 10  Match: 2   EEE,FFF
Round: 10  Match: 3   JJJ,MMM
Round: 10  Match: 4   QQQ,BBB
Round: 10  Match: 5   PPP,HHH
Round: 10  Match: 6   NNN,KKK
Round: 10  Match: 7   GGG,LLL
Round: 10  Match: 8   III,OOO
Round: 10  Match: 9   RRR,DDD
Round: 11  Match: 1   HHH,NNN
Round: 11  Match: 2   III,JJJ
Round: 11  Match: 3   KKK,MMM
Round: 11  Match: 4   BBB,OOO
Round: 11  Match: 5   RRR,LLL
Round: 11  Match: 6   GGG,DDD
Round: 11  Match: 7   QQQ,CCC
Round: 11  Match: 8   FFF,AAA
Round: 11  Match: 9   EEE,PPP
Round: 12  Match: 1   BBB,NNN
Round: 12  Match: 2   MMM,GGG
Round: 12  Match: 3   KKK,JJJ
Round: 12  Match: 4   FFF,CCC
Round: 12  Match: 5   AAA,DDD
Round: 12  Match: 6   III,EEE
Round: 12  Match: 7   LLL,PPP
Round: 12  Match: 8   HHH,OOO
Round: 12  Match: 9   QQQ,RRR
Round: 13  Match: 1   MMM,KKK
Round: 13  Match: 2   FFF,RRR
Round: 13  Match: 3   BBB,GGG
Round: 13  Match: 4   QQQ,EEE
Round: 13  Match: 5   CCC,HHH
Round: 13  Match: 6   AAA,PPP
Round: 13  Match: 7   OOO,III
Round: 13  Match: 8   DDD,JJJ
Round: 13  Match: 9   NNN,LLL
Round: 14  Match: 1   KKK,OOO
Round: 14  Match: 2   PPP,GGG
Round: 14  Match: 3   JJJ,FFF
Round: 14  Match: 4   BBB,HHH
Round: 14  Match: 5   LLL,NNN
Round: 14  Match: 6   DDD,AAA
Round: 14  Match: 7   MMM,RRR
Round: 14  Match: 8   CCC,EEE
Round: 14  Match: 9   QQQ,III
Round: 15  Match: 1   PPP,FFF
Round: 15  Match: 2   BBB,AAA
Round: 15  Match: 3   OOO,QQQ
Round: 15  Match: 4   JJJ,CCC
Round: 15  Match: 5   MMM,LLL
Round: 15  Match: 6   EEE,RRR
Round: 15  Match: 7   NNN,GGG
Round: 15  Match: 8   III,HHH
Round: 15  Match: 9   DDD,KKK
Round: 16  Match: 1   RRR,HHH
Round: 16  Match: 2   JJJ,KKK
Round: 16  Match: 3   EEE,LLL
Round: 16  Match: 4   DDD,GGG
Round: 16  Match: 5   NNN,OOO
Round: 16  Match: 6   MMM,CCC
Round: 16  Match: 7   AAA,BBB
Round: 16  Match: 8   PPP,III
Round: 16  Match: 9   FFF,QQQ
Round: 17  Match: 1   MMM,HHH
Round: 17  Match: 2   LLL,EEE
Round: 17  Match: 3   CCC,JJJ
Round: 17  Match: 4   GGG,III
Round: 17  Match: 5   PPP,KKK
Round: 17  Match: 6   DDD,BBB
Round: 17  Match: 7   NNN,QQQ
Round: 17  Match: 8   AAA,FFF
Round: 17  Match: 9   RRR,OOO

PL/SQL procedure successfully completed.

SQL>
SQL> select home_team||away_team
  2  from fixture
  3  group by home_team||away_team
  4  having count(*) > 1;

no rows selected

SQL>
SQL> select round, team
  2  from (
  3  select round, home_team team
  4  from fixture
  5  union all
  6  select round, away_team
  7  from fixture
  8  )
  9  group by round, team
 10  having count(*) > 1;

no rows selected

SQL>
SQL> select home_team, count(*)
  2  from fixture
  3  group by home_team
  4  order by 2;

HOME_TEAM         COUNT(*)
--------------- ----------
GGG                      8
CCC                      8
PPP                      8
AAA                      8
DDD                      8
OOO                      8
NNN                      8
HHH                      8
RRR                      8
QQQ                      9
JJJ                      9
BBB                      9
FFF                      9
MMM                      9
III                      9
KKK                      9
LLL                      9
EEE                      9

18 rows selected.

SQL>
SQL> select away_team, count(*)
  2  from fixture
  3  group by away_team
  4  order by 2;

AWAY_TEAM         COUNT(*)
--------------- ----------
MMM                      8
BBB                      8
JJJ                      8
KKK                      8
LLL                      8
QQQ                      8
FFF                      8
III                      8
EEE                      8
PPP                      9
OOO                      9
CCC                      9
NNN                      9
GGG                      9
AAA                      9
HHH                      9
RRR                      9
DDD                      9

18 rows selected.

SQL>
SQL>
复制