php - 如何使用MySQL计算最长的不败纪录？

Question

我正在创建一个网页，其中列出了单个团队的各种最长连胜纪录，但在尝试为所涉及的结果类型组合计算连胜纪录时遇到了一个问题。

数据如下表所示...

date         result   gf   ga   type     compfull--------------------------------------------------1980-08-16      W     3    0   league    Division 11980-08-19      L     1    2   league    Division 11980-08-23      W     3    1   league    Division 11980-08-26      W     2    0   league    Division 11980-08-30      D     2    2   league    Division 1and so on...

Using the following query (or similar), I can determine the longest run of wins or losses or even games scored in.

SELECT result, type, MIN(date) as StartDate, MAX(date) as EndDate, COUNT(*) as Games 
FROM (SELECT result, type, date, compfull, (SELECT COUNT(*) 
FROM resultengine R 
WHERE R.result <> RE.result
AND R.date <= RE.date) as RunGroup
FROM resultengine RE) A WHERE result='W' GROUP BY result, RunGroup ORDER BY Games

这是基于我发现的优秀资源 here .它完全按照我的意愿行事，并告诉我我想知道的事情 - 如果我改为

result='L'

我无法解决的是如何计算最长的连续不败场次，即没有 L 的最长场次。反之亦然。

我尝试了以下查询但无济于事:

SELECT result, type, MIN(date) as StartDate, MAX(date) as EndDate, COUNT(*) as Games 
FROM (SELECT result, type, date, compfull, (SELECT COUNT(*) 
FROM resultengine R 
WHERE R.result <> RE.result
AND R.date <= RE.date) as RunGroup
FROM resultengine RE) A WHERE result!='W' GROUP BY result, RunGroup ORDER BY Games

我还尝试将查询更改为:

WHERE result='W' OR result='D'

同样，这不起作用。这两种尝试都反射(reflect)了用于传递最长连续 Ws 或 Ls 的查询——我的数据的手动计数告诉我这是不正确的。毫无疑问，我在这里遗漏了一些简单的东西，但我如何才能执行该查询，以便它告诉我使用 Ls 或 Ws 的最长连续结果？

Answer 1

根据您的代码示例进行扩展，以下给出了获胜/平局的运行。问题出在“WHERE R.result <> RE.result”，它总是为任何不同的结果代码分配不同的分组。在这里，我更改了该子句(和其他一些子句)以将“W”和“D”组合在一起以生成单个代码:

SELECT result, TYPE, MIN(DATE) AS StartDate, MAX(DATE) AS EndDate, COUNT(*) AS Games 
FROM (SELECT result, TYPE, DATE, compfull, (SELECT COUNT(*) 
  FROM resultengine R 
  WHERE IF(R.result IN ('W','D'),1,0) <> IF(RE.result IN ('W','D'),1,0)
  AND R.date <= RE.date) AS RunGroup
FROM resultengine RE) A WHERE result IN ('W','D') GROUP BY IF(result IN ('W','D'),1,0), RunGroup ORDER BY Games