如何使用MySQL计算最长的不败连胜? [英] How to calculate longest undefeated streak using MySQL?

问题描述

我正在创建一个列出单个团队的最长条纹的网页，但是在尝试为涉及的结果类型组合计算条纹时遇到了一个问题.

I'm creating a web page that lists various longest streaks for a single team, but I've encountered an issue when trying to calculate a streak for a combination of result types involved.

数据显示在下表中...

The data is presented in a table as follows...

date         result   gf   ga   type     compfull
--------------------------------------------------
1980-08-16      W     3    0   league    Division 1
1980-08-19      L     1    2   league    Division 1
1980-08-23      W     3    1   league    Division 1
1980-08-26      W     2    0   league    Division 1
1980-08-30      D     2    2   league    Division 1
and 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

这基于我在此处找到的出色资源.它确实满足我的要求，并且告诉我我想知道的内容-如果我更改为

This is based on the excellent resource I found here. It does exactly what I want it to and tells me what I want to know - the same applies if I change to

result='L'

我无法解决的是如何计算不败游戏的最长连胜纪录，即没有L的最长连胜纪录.反之亦然.

What I can't work out is how to calculate the longest streak of undefeated games i.e. the longest run without an L. And it's the same vice-versa.

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

I've tried the following query to no avail:

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

我还尝试将查询更改为:

I've also tried altering the query to:

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

同样，这不起作用.两次尝试都反映了用于提供最长连读的Ws或Ls的查询-手动计数我的数据告诉我这是不正确的.毫无疑问，我在这里缺少一些简单的东西，但是如何执行该查询，以便告诉我使用Ls或Ws的最长结果记录?

Again, this doesn't work. Both attempts mirror the query that's used to deliver the longest streak of Ws or Ls - and a manual count of my data tells me that isn't correct. I'm no doubt missing something simple here, but how can I execute that query so it tells me the longest streak of results with either Ls or Ws?

推荐答案

从您的代码示例扩展而来，以下给出了获胜/平局.问题在于"WHERE R.result<> RE.result"，它总是为任何不同的结果代码分配不同的分组.在这里，我将该子句(和其他一些子句)更改为将"W"和"D"分组在一起以编写单个代码:

Extended from your code sample the following gives runs of wins/draws. The problem was with 'WHERE R.result <> RE.result' which always assigned different groupings for any different result code. Here I change that clause (and some others) to group 'W' and 'D' together to make a single code:

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

这篇关于如何使用MySQL计算最长的不败连胜?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！