有一表如下:
ID TIME VALUE
----------- ----------------------- -----
1 2007-08-27 00:00:05.747 0
2 2007-08-27 00:01:03.327 1
3 2007-08-27 00:02:08.343 0
4 2007-08-27 00:03:18.403 1
5 2007-08-27 00:04:18.827 0
6 2007-08-27 00:05:21.967 0
7 2007-08-27 00:06:22.390 1
8 2007-08-27 00:07:27.857 0
9 2007-08-27 00:08:31.263 1
10 2007-08-27 00:09:39.047 1
11 2007-08-27 00:10:38.983 1
12 2007-08-27 00:11:42.263 1
13 2007-08-27 00:12:48.437 0
14 2007-08-27 00:13:56.043 1
15 2007-08-27 00:15:00.997 0
16 2007-08-27 00:16:01.200 1
17 2007-08-27 00:17:09.750 1
18 2007-08-27 00:18:14.670 1
19 2007-08-27 00:19:13.937 1
20 2007-08-27 00:20:12.390 1现在想统计VALUE连续出现的最大次数,
例如:当VALUE=0时:最多连续2次
当VALUE=1时:最多连续5次
请问这个语句可以实现吗?
ID TIME VALUE
----------- ----------------------- -----
1 2007-08-27 00:00:05.747 0
2 2007-08-27 00:01:03.327 1
3 2007-08-27 00:02:08.343 0
4 2007-08-27 00:03:18.403 1
5 2007-08-27 00:04:18.827 0
6 2007-08-27 00:05:21.967 0
7 2007-08-27 00:06:22.390 1
8 2007-08-27 00:07:27.857 0
9 2007-08-27 00:08:31.263 1
10 2007-08-27 00:09:39.047 1
11 2007-08-27 00:10:38.983 1
12 2007-08-27 00:11:42.263 1
13 2007-08-27 00:12:48.437 0
14 2007-08-27 00:13:56.043 1
15 2007-08-27 00:15:00.997 0
16 2007-08-27 00:16:01.200 1
17 2007-08-27 00:17:09.750 1
18 2007-08-27 00:18:14.670 1
19 2007-08-27 00:19:13.937 1
20 2007-08-27 00:20:12.390 1现在想统计VALUE连续出现的最大次数,
例如:当VALUE=0时:最多连续2次
当VALUE=1时:最多连续5次
请问这个语句可以实现吗?
insert into @t values( 1,'2007-08-27 00:00:05.747',0)
insert into @t values( 2,'2007-08-27 00:01:03.327',1)
insert into @t values( 3,'2007-08-27 00:02:08.343',0)
insert into @t values( 4,'2007-08-27 00:03:18.403',1)
insert into @t values( 5,'2007-08-27 00:04:18.827',0)
insert into @t values( 6,'2007-08-27 00:05:21.967',0)
insert into @t values( 7,'2007-08-27 00:06:22.390',1)
insert into @t values( 8,'2007-08-27 00:07:27.857',0)
insert into @t values( 9,'2007-08-27 00:08:31.263',1)
insert into @t values(10,'2007-08-27 00:09:39.047',1)
insert into @t values(11,'2007-08-27 00:10:38.983',1)
insert into @t values(12,'2007-08-27 00:11:42.263',1)
insert into @t values(13,'2007-08-27 00:12:48.437',0)
insert into @t values(14,'2007-08-27 00:13:56.043',1)
insert into @t values(15,'2007-08-27 00:15:00.997',0)
insert into @t values(16,'2007-08-27 00:16:01.200',1)
insert into @t values(17,'2007-08-27 00:17:09.750',1)
insert into @t values(18,'2007-08-27 00:18:14.670',1)
insert into @t values(19,'2007-08-27 00:19:13.937',1)
insert into @t values(20,'2007-08-27 00:20:12.390',1)select
VALUE,max(NUM) as NUM
from
(select
min(b.ID)-a.ID+1 as NUM,a.VALUE
from
(select t.* from @t t where not exists(select 1 from @t where ID=t.ID-1 and VALUE=t.VALUE)) a,
(select t.* from @t t where not exists(select 1 from @t where ID=t.ID+1 and VALUE=t.VALUE)) b
where
a.ID<=b.ID and a.VALUE=b.VALUE
group by
a.ID,a.VALUE) n
group by
VALUE/*
VALUE NUM
----------- -----------
0 2
1 5
*/
declare @t table(ID int,TIME datetime,VALUE int)
insert into @t values( 1,'2007-08-27 00:00:05.747',0)
insert into @t values( 2,'2007-08-27 00:01:03.327',1)
insert into @t values( 3,'2007-08-27 00:02:08.343',0)
insert into @t values( 4,'2007-08-27 00:03:18.403',1)
insert into @t values( 5,'2007-08-27 00:04:18.827',0)
insert into @t values( 6,'2007-08-27 00:05:21.967',0)
insert into @t values( 7,'2007-08-27 00:06:22.390',1)
insert into @t values( 8,'2007-08-27 00:07:27.857',0)
insert into @t values( 9,'2007-08-27 00:08:31.263',1)
insert into @t values(10,'2007-08-27 00:09:39.047',1)
insert into @t values(11,'2007-08-27 00:10:38.983',1)
insert into @t values(12,'2007-08-27 00:11:42.263',1)
insert into @t values(13,'2007-08-27 00:12:48.437',0)
insert into @t values(14,'2007-08-27 00:13:56.043',1)
insert into @t values(15,'2007-08-27 00:15:00.997',0)
insert into @t values(16,'2007-08-27 00:16:01.200',1)
insert into @t values(17,'2007-08-27 00:17:09.750',1)
insert into @t values(18,'2007-08-27 00:18:14.670',1)
insert into @t values(19,'2007-08-27 00:19:13.937',1)
insert into @t values(20,'2007-08-27 00:20:12.390',1)
select value,max(次数)[最多次数] from
(select VALUE,count(col)[次数]
from
(
select
*,col=(select count(1) from @t where ID<a.ID and VALUE<>a.VALUE)
from @t a
)a
group by VALUE,col)g group by VALUE
--不用ID
declare @table table (xtime datetime,value1 varchar(1))
insert into @table
select '2007-08-27 00:00:05.747','0'
union all
select '2007-08-27 00:01:03.327','1'
union all
select '2007-08-27 00:02:08.343','0'
union all
select '2007-08-27 00:03:18.403','1'
union all
select '2007-08-27 00:04:18.827','1'
union all
select '2007-08-27 00:05:21.967','1'
union all
select '2007-08-27 00:06:22.390','0'
union all
select '2007-08-27 00:07:27.857','0'
union all
select '2007-08-27 00:08:31.263','1'
select value1 ,max(amount) num
from (
select grp,value1,count(grp) amount
from (
SELECT xtime, value1, (SELECT MIN(xtime) FROM @table AS V2 WHERE V2.value1 <> V1.value1 AND V2.xtime > V1.xtime) AS grp FROM @table AS V1
) a
group by grp,value1
) c
group by value1