mysql - 编写mySQL以获得序列

我需要确定特定运动队得失的顺序。例如：

表：结果

teamid    win  eventdate
======    ===  =========
1         0    26/01/2014
1         0    25/01/2014
2         1    26/01/2014
1         0    24/01/2014
1         1    21/01/2014

它应该返回队号1并丢失3个序列。但是如何在sql中做到这一点？

最佳答案

MySQL不支持分析功能，但是一种计算连续赢/输的方法是通过user variables：

SELECT   @seq    := IF(@teamid<=>teamid AND @win<=>win,@seq,0)+1 seq,
         @teamid := teamid teamid,
         @win    := win win
FROM     RESULTS, (SELECT @teamid:=NULL, @win:=NULL) init
ORDER BY teamid, STR_TO_DATE(eventdate, '%d/%m/%Y')

该查询可以用作执行进一步分析的基础。例如，要获得球队最长的连胜纪录（包括输赢）：

SELECT teamid, win, MAX(seq) FROM (
  SELECT   @seq    := IF(@teamid<=>teamid AND @win<=>win,@seq,0)+1 seq,
           @teamid := teamid teamid,
           @win    := win win
  FROM     RESULTS, (SELECT @teamid:=NULL, @win:=NULL) init
  ORDER BY teamid, STR_TO_DATE(eventdate, '%d/%m/%Y')
) t GROUP BY teamid, win

关于mysql - 编写mySQL以获得序列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21368639/

teamID

mysql - 编写mySQL以获得序列