MySQL-用条件连续计数日期并获取范围列表 [英] Mysql - Count date consecutive with condition and get range list
问题描述
我需要知道一个团队连续赢得多少个日期(值1>值2),并知道第一场和最后一场比赛获胜的日期.并显示按赢家和日期排列的10个范围顺序的列表.我知道这并不容易.这对我来说并不容易.我尝试进行一些查询和子查询,但结果不佳.谢谢我有一张桌子
I need to know how many dates a team won (value1 > value2) consecutively and know the date of the first and the last match played won. And show a list of 10 ranges order by won and date. I know its not easy. Its not easy to me.. I try to make some queries and subquery but without good results. Thanks I have a table With
CREATE TABLE `games` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`value1` int(11) NOT NULL,
`value2` int(11) NOT NULL,
`played` date NOT NULL,
PRIMARY KEY (`id`),
KEY `played` (`played`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
insert into games (value1, value2, played)
values
("2", "3", "1943-05-09"),
("4", "3", "1943-08-15"),
("1", "8", "1943-08-22"),
("0", "4", "1943-08-29"),
("1", "0", "1943-09-12"),
("1", "3", "1943-09-26"),
("6", "1", "1943-10-03"),
("3", "2", "1943-10-10"),
("3", "3", "1944-07-16"),
("1", "1", "1944-08-06"),
("4", "1", "1944-09-24"),
("0", "7", "1944-10-08"),
("0", "1", "1945-05-13"), // 1
("4", "2", "1945-11-04"), // 2
("3", "2", "1946-05-12"), // 3 second 3 consecutives win
("4", "2", "1946-11-17"),
("2", "2", "1946-11-24"),
("1", "5", "1946-12-01"),
("1", "0", "1947-05-18"),
("3", "0", "1947-10-05"),
("2", "3", "1948-11-07"),
("0", "1", "1948-11-14"),
("1", "4", "1948-11-21"),
("3", "1", "1949-06-12"), // 1
("4", "0", "1949-06-19"), // 2
("5", "1", "1949-07-24"), // 3
("3", "1", "1949-08-06") // 4 first consecutives win
我需要这样的结果
From To games_won
1949-06-12 1949-08-06 4
1945-11-04 1946-11-17 3
1943-10-03 1943-10-10 2
...
...
推荐答案
您可以使用变量来计算获胜次数和行数.对于连续获胜而言,差异是恒定的.这将为您提供一个汇总值,以获取最终结果:
You can count the number of wins and the number of rows using variables. The difference is constant for consecutive wins. This gives you a value for aggregation to get the final result:
select min(played), max(played), count(*)
from (select g.*,
(@rn := @rn + 1) as rn,
(@rnw := if(value1 > value2, @rnw + 1, @rnw) as rnw
from games g cross join
(select @rn := 0, @rnw := 0) params
order by played
) g
where value1 > value2
group by (rn - rnw);
这篇关于MySQL-用条件连续计数日期并获取范围列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!