ID TYPE TIME
1 0 2011-01-01 01:01:01
2 0 2011-01-01 01:02:01
3 1 2011-01-01 01:03:01
4 1 2011-01-01 01:04:01
5 0 2011-01-01 01:05:01
6 0 2011-01-01 01:06:01
7 0 2011-01-01 01:07:01求在表中按TIME DESC 后,TYPE为0的求和直至TYPE为1 结果为3请问用sql怎么实现
1 0 2011-01-01 01:01:01
2 0 2011-01-01 01:02:01
3 1 2011-01-01 01:03:01
4 1 2011-01-01 01:04:01
5 0 2011-01-01 01:05:01
6 0 2011-01-01 01:06:01
7 0 2011-01-01 01:07:01求在表中按TIME DESC 后,TYPE为0的求和直至TYPE为1 结果为3请问用sql怎么实现
from tablename
where time >(select top 1 time from tablename order by time desc)
from tablename
where time >(select top 1 time from tablename where type <> 0 order by time desc)
select count(id)
from tb
where id > (select top 1 id from tb where type = 1 order by time desc)
insert tb
select 1 ,0 ,'2011-01-01 01:01:01' union all
select 2 ,0 ,'2011-01-01 01:02:01' union all
select 3 ,1 ,'2011-01-01 01:03:01' union all
select 4 ,1 ,'2011-01-01 01:04:01' union all
select 5 ,0 ,'2011-01-01 01:05:01' union all
select 6 ,0 ,'2011-01-01 01:06:01' union all
select 7 ,0 ,'2011-01-01 01:07:01' select * from tb a
where type =0
and not exists (select 1 from tb where type=1 and time>a.time) /*
ID TYPE TIME
----------- ----------- ------------------------------------------------------
5 0 2011-01-01 01:05:01.000
6 0 2011-01-01 01:06:01.000
7 0 2011-01-01 01:07:01.000(所影响的行数为 3 行)
*/