mysql - 如何使用 mySQL 按团队和结果检查条纹？

Question

我创建了一个包含比赛 ID、团队、结果（使用“W”、“D”、“L”）和日期的视图。例如：

'32750', 'Team 1', 'D', '2019-02-16 21:30:00'
'32750', 'Team 2', 'D', '2019-02-16 21:30:00'
'32748', 'Team 3', 'L', '2019-02-16 19:20:00'
'32748', 'Team 4', 'W', '2019-02-16 19:20:00'

我调整了我在此处找到的查询，该查询使用该表计算连胜，并向我显示团队、连胜的开始日期、结束日期、连胜计数以及该连胜中包含的匹配项。

'Team 1', '1992-07-05', '1992-09-06', '5', '9522,9142,9161,9167,9180'

我遇到的问题是我必须在 where 子句中包含团队名称，否则查询显示不正确的结果。这是我用于 Winning Streaks 的查询

select 
   team_name,
   min(match_date) as start_date,
   max(match_date) as end_date,
   count(match_date) as streak,
   group_concat(idmatch) as gameid_list
from
( 
  select *,      
    IF(
        result = "W"
        and 
        @result = "W", 
           @gn, @gn := @gn + 1)                
    as group_number,

    @match_date as old_date, @idmatch as old_gameid, 
    @result as old_rersult,

    @match_date := match_date, @idmatch := idmatch, 
    @result := result    
  from my_football_database.`Resultados Globales WDL`
  cross join 
  (
    select 
      @match_date := CAST(null as date) as xa,
      @idmatch := null + 0 as xb,
      @result := null + 0 as xc, @gn := 0
  ) x where team_name = "Team 1"
  order by match_date
) as y
group by group_number
order by streak desc;

如您所见，我必须在 where 子句中指定球队的名称，所以如果我想获得历史上前 3 名的连胜记录，我必须为每个球队运行此查询 150 次，然后手动比较.

我怎样才能修改它以获得按团队正确分组的完整的条纹列表？

标签： mysql