MySQL连续数统计

来源：互联网发布：samlight打标软件编辑：程序博客网时间：2024/04/27 15:25

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的数。比如上面统计结果应该是

1 B211001024014 4
2 B211001024011 3
3 B211001024013 2

SQL如下

SELECT yhh,MAX(s) nums from (danielinbitiselect 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

0 0