如何查找最长的连续结果集？

Question

SELECT team1, score1, score2, team2, gameDate 
FROM GAMES 
WHERE team1 = 'Tottenham Hotspur' 
    OR team2 = 'Tottenham Hotspur' 
ORDER BY gameDate ASC; 

该查询返回游戏结果和比赛日期（“托特纳姆热刺”）的比赛日期排序。

team1    score1 score2 team2      gameDate 
+-------------------------------------------------------------------------+ 
Tottenham Hotspur  0  0  Manchester City   2010-08-14 
Stoke City    1  2  Tottenham Hotspur   2010-08-21 
Tottenham Hotspur  0  1  Wigan Athletic   2010-08-28 
West Bromwich Albion 1  1  Tottenham Hotspur   2010-09-11 
Tottenham Hotspur  3  1  Wolverhampton Wanderers 2010-09-18 
West Ham United  1  0  Tottenham Hotspur   2010-09-25 
Tottenham Hotspur  2  1  Aston Villa    2010-10-02 
Fulham     1  2  Tottenham Hotspur   2010-10-16 
Tottenham Hotspur  1  1  Everton     2010-10-23 
Manchester United  2  0  Tottenham Hotspur   2010-10-30 
Bolton Wanderers  4  2  Tottenham Hotspur   2010-11-06 
Tottenham Hotspur  1  1  Sunderland    2010-11-09 

如何在这里找到最长的连续赢/输结果集？

在上面的例子中，最高连续4个游戏（游戏按日期排序）没有平局的结果，我们要过滤这些游戏

team1    score1 score2 team2      gameDate 
+-------------------------------------------------------------------------+ 
Tottenham Hotspur  3  1  Wolverhampton Wanderers 2010-09-18 
West Ham United  1  0  Tottenham Hotspur   2010-09-25 
Tottenham Hotspur  2  1  Aston Villa    2010-10-02 
Fulham     1  2  Tottenham Hotspur   2010-10-16 

Answer 1

尝试这个：

SELECT tttt.* from (
    SELECT ttt.*, RANK() over(order by res desc, seqcnt desc , grp desc) rnk from (
     SELECT tt.*, count(*) over(partition by grp, res) as seqcnt from (
      SELECT t.*, ROW_NUMBER() over(order by gameDate) - ROW_NUMBER() over(partition by res order by gameDate) as grp from (
       SELECT team1, score1, score2, team2, gameDate, case when score1= score2 then 'D' else 'N' end as res 
       FROM GAMES WHERE team1 = 'Tottenham Hotspur' OR team2 = 'Tottenham Hotspur' 
      ) t 
     ) tt 
    ) ttt 
) tttt WHERE rnk = 1 order by gameDate 

Answer 2

你可以尝试

SELECT team1, score1, score2, team2, gameDate 
FROM GAMES 
WHERE team1 = 'Tottenham Hotspur' 
    OR team2 = 'Tottenham Hotspur' 
and score1<>score2 
ORDER BY gameDate ASC