mysql简单递归

mysql ldboyghg 26℃ 0评论
CREATE TABLE `pd` (
`imei` varchar(32) NOT NULL DEFAULT ”,
`dat` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– —————————-
– Records of pd
– —————————-
INSERT INTO `pd` VALUES (‘1′, ‘2013-07-25 00:00:01′);
INSERT INTO `pd` VALUES (‘1′, ‘2013-07-26 00:00:02′);
INSERT INTO `pd` VALUES (‘2′, ‘2013-07-23 00:00:04′);
INSERT INTO `pd` VALUES (‘2′, ‘2013-07-26 00:00:03′);
INSERT INTO `pd` VALUES (‘3′, ‘2013-07-26 00:00:01′);
select * from (
select imei user_id, max(max_dd) , max(max_dd_2), to_days( max(max_dd)) – to_days(max(max_dd_2)) days from (
select imei, max_dd, max_dd_2 from (
select tmp.imei, tmp.dates,
if(@imei=tmp.imei, @rank:=@rank+1,@rank:=1) as rank,
if(@rank = 1, @max_d := tmp.dates, @max_d := null) as max_dd,
if(@rank = 2, @max_d_2 := tmp.dates, @max_d_2 := null) as max_dd_2,
@imei:=tmp.imei
from ( select imei,dat dates from pd order by imei asc ,dat desc ) tmp ,
(select @rownum :=0 , @imei := null ,@rank:=0, @max_d :=null, @max_d_2 := null) a
) result
) t
group by imei
having count(*) > 1
) x where x.days >= 1 and EXISTS (select ‘x’ from pd where dat > ‘2013-07-26 00:00:00′)

转载请注明:生命不息,奋斗不止 » mysql简单递归

喜欢 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址