关于MySQL连续为0检查的问题

Question

CREATE TABLE [dbo].Jqfk NOT NULL,
[yhh] char NULL,CREATE TABLE [dbo].Jqfk NOT NULL,
[yhh] char NULL,
[cbrq] nvarchar NULL,
[ysl] [int] NULL,
)

[cbrq] [nvarchar](19) NULL,
[ysl] [int] NULL,

)

INSERT INTO jqfk VALUES ('B173022031201','2015-01-08 10:12:30',0);
INSERT INTO jqfk VALUES ('B211001024011','2015-01-08 10:46:13',13);
INSERT INTO jqfk VALUES ('B211001024012','2015-01-08 10:46:13',0);
INSERT INTO jqfk VALUES ('B211001024013','2015-01-08 10:46:13',3);
INSERT INTO jqfk VALUES ('B211001024014','2015-01-08 10:46:13',0);

INSERT INTO jqfk VALUES ('B173022031201','2015-02-08 10:46:13',0);
INSERT INTO jqfk VALUES ('B211001024011','2015-02-08 10:46:13',0);
INSERT INTO jqfk VALUES ('B211001024012','2015-02-08 10:46:13',13);
INSERT INTO jqfk VALUES ('B211001024013','2015-02-08 10:46:13',11);
INSERT INTO jqfk VALUES ('B211001024014','2015-02-08 10:46:13',0);

INSERT INTO jqfk VALUES ('B173022031201','2015-03-08 10:46:13',0);
INSERT INTO jqfk VALUES ('B211001024011','2015-03-08 10:46:13',0);
INSERT INTO jqfk VALUES ('B211001024012','2015-03-08 10:46:13',0);
INSERT INTO jqfk VALUES ('B211001024013','2015-03-08 10:46:13',0);
INSERT INTO jqfk VALUES ('B211001024014','2015-03-08 10:46:13',0);

INSERT INTO jqfk VALUES ('B173022031201','2015-04-08 10:46:13',13);
INSERT INTO jqfk VALUES ('B211001024011','2015-04-08 10:46:13',0);
INSERT INTO jqfk VALUES ('B211001024012','2015-04-08 10:46:13',12);
INSERT INTO jqfk VALUES ('B211001024013','2015-04-08 10:46:13',0);
INSERT INTO jqfk VALUES ('B211001024014','2015-04-08 10:46:13',0);

我需要最后一次为0，且之后按时间倒序排序为0 的次数。其实需要的是最近一次为0的用户，他最近为0次数。

结果

1 B211001024014 4
2 B211001024011 3
3 B211001024013 2

Answer 1

写了一下，用下面语句
 SELECT yhh,MAX(s) nums from (
select a.*
,case when @f=0 then @cust:=yhh end t1
,@f:=1
,case when yhh!=@cust then @flag:=0 end t
,case when ysl!=0 and @flag=0 then @flag:=1 end f
,case when ysl=0 and @flag=0 then @cnt:=@cnt+1 else @cnt:=0 end c
,@cnt s,@cust:=yhh,@flag f2 from (
select * from jqfk order by yhh,cbrq desc 
) a,(select @cust:='') r,(select @cnt:=0) r2,(select @flag:=0) r3,(select @f:=0) r4
) k where f2=0 group by yhh order by nums desc