我正在创建一个列出单个团队的最长条纹的网页,但是在尝试为涉及的结果类型组合计算条纹时遇到了一个问题.
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计算最长的不败连胜?
发布评论