MySQL序列创建

Question

我需要一些帮助来提取在 MySQL 环境中按顺序发生的记录。

我的数据集由越野比赛和输赢国家组成。我需要确定连续赢得至少 3 场比赛的国家/地区。下面是一个可重现的例子。我创建了一个匹配数据集。

    CREATE TABLE matches (date DATE, winner CHAR(10), loser CHAR(10));
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-03-2013', '%m-%d-%Y') ,'USA','CHINA');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-05-2013', '%m-%d-%Y') ,'USA','RUSSIA');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-06-2013', '%m-%d-%Y') ,'FRANCE','GERMANY');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-09-2013', '%m-%d-%Y') ,'USA','RUSSIA');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-11-2013', '%m-%d-%Y') ,'USA','INDIA');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-15-2013', '%m-%d-%Y') ,'USA','AUSTRALIA');
    INSERT INTO matches (date,winner,loser) VALUES (STR_TO_DATE('3-15-2013', '%m-%d-%Y') ,'USA','NEW ZEALAND');

我创建了另一个数据集，其中每个国家/地区都有一个按日期排序的行号。

    CREATE TABLE matches2 
    (
         date DATE, 
         winner CHAR(10),
         loser CHAR(10),
         row INT
     );
    INSERT INTO matches2 
    (
        row,
        winner,
        date,
        loser
    )
    SELECT row,
           winner,
           date , 
           loser
    FROM 
    (
       SELECT winner, 
              (@winner:=@winner+1) AS row, 
               date ,
               loser
       FROM matches ,
       (SELECT @winner := 0) r
    ) x
    ORDER BY date;

表 matches2 如下所示

   date         winning   losing        row
   2013-03-03   USA       CHINA          1
   2013-03-05   USA       RUSSIA         2
   2013-03-06   FRANCE    GERMANY        3
   2013-03-09   USA       RUSSIA         4
   2013-03-11   USA       INDIA          5
   2013-03-15   USA       AUSTRALIA      6
   2013-03-15   USA       NEW ZEALAN     7

数据显示，美国队已经连续赢了 >3 场比赛。我如何编写代码来捕获此序列？

Answer 1

您可以通过一系列连接来做到这一点:

select m1.*, m2.date, m3.date
from matches2 m1 join
     matches2 m2
     on m2.row = m1.row + 1 and m2.winner = m1.winner join
     matches2 m3
     on m3.row = m2.row + 1 and m3.winner = m2.winner join
     matches2 m4
     on m4.row = m3.row + 1 and m4.winner = m3.winner;