56,679
社区成员
发帖
与我相关
我的任务
分享
create table m
(
`de` varchar(10),
`ar` varchar(10),
`detime` DATE,
`artime` date
)
engine = InnoDB auto_increment = 1 DEFAULT charset = utf8mb4
INSERT INTO m VALUES('北京','广州','20160901','20160902'),
('北京','广州','20160903','20160906'),
('北京','广州','20160907','20160908'),
('北京','上海','20160909','20160910'),
('北京','深圳','20160911','20160915'),
('北京','深圳','20160917','20160920'),
('北京','广州','20160922','20160924'),
('北京','广州','20160925','20160926')
SELECT de,
ar,
min(detime),
max(artime)
from m
GROUP BY 1,2
SELECT result.number,
result.de,
result.ar,
min(result.detime),
max(result.artime)
FROM
(
select b.de,b.ar,b.detime,b.artime,
if(b.de=@x and b.ar = @y,@num:=@num,@num:=@num+1) as number,
@x:=b.de,
@y:=b.ar
from (select de,ar,detime,artime from m order by detime,artime ) b ,
(select @x := null , @y := NULL, @num:=0) c
)result
GROUP BY 1,2,3