set nocount on
create table tb(代码 int, 时间 datetime,jg int)
insert into tb values(1 ,'2008-4-20', 35 )
insert into tb values(1 ,'2008-4-13', 77 )
insert into tb values(1 ,'2008-4-10', 35 )
insert into tb values(1 ,'2008-4-6', 43 )
insert into tb values(1 ,'2008-4-2', 35 )
insert into tb values(1 ,'2008-3-30', 35 )
insert into tb values(2 ,'2008-3-23', 35 )
insert into tb values(1 ,'2008-3-23', 35 )
insert into tb values(2 ,'2008-3-20', 35 )
insert into tb values(1 ,'2008-3-15', 89 )
insert into tb values(1 ,'2008-3-8', 35 )
insert into tb values(1 ,'2008-3-5', 35 )
insert into tb values(1 ,'2008-3-3', 35 )
insert into tb values(1 ,'2008-3-1', 35 )
insert into tb values(1 ,'2008-2-8', 35 )
insert into tb values(1 ,'2008-2-5', 35 )
insert into tb values(1 ,'2008-2-3', 35 )
insert into tb values(1 ,'2008-2-1', 35 )
insert into tb values(2 ,'2008-1-4', 35 )goselect px = identity(int,1,1) ,* into # from tbselect *
into #1
from # c
where exists(select 1 from # where c.代码 = 代码 and px=c.px-1 and jg = 35) and jg = 35
select count(1) + 2
from #1 awhere exists (select 1 from #1
where px = a.px + 1 )drop table tb,#,#1/*
-----------
8
*/
create table tb(代码 int, 时间 datetime,jg int)
insert into tb values(1 ,'2008-4-20', 35 )
insert into tb values(1 ,'2008-4-13', 77 )
insert into tb values(1 ,'2008-4-10', 35 )
insert into tb values(1 ,'2008-4-6', 43 )
insert into tb values(1 ,'2008-4-2', 35 )
insert into tb values(1 ,'2008-3-30', 35 )
insert into tb values(2 ,'2008-3-23', 35 )
insert into tb values(1 ,'2008-3-23', 35 )
insert into tb values(2 ,'2008-3-20', 35 )
insert into tb values(1 ,'2008-3-15', 89 )
insert into tb values(1 ,'2008-3-8', 35 )
insert into tb values(1 ,'2008-3-5', 35 )
insert into tb values(1 ,'2008-3-3', 35 )
insert into tb values(1 ,'2008-3-1', 35 )
insert into tb values(1 ,'2008-2-8', 35 )
insert into tb values(1 ,'2008-2-5', 35 )
insert into tb values(1 ,'2008-2-3', 35 )
insert into tb values(1 ,'2008-2-1', 35 )
insert into tb values(2 ,'2008-1-4', 35 )goselect px = identity(int,1,1) ,* into # from tbselect *
into #1
from # c
where exists(select 1 from # where c.代码 = 代码 and px=c.px-1 and jg = 35) and jg = 35
select count(1) + 2
from #1 awhere exists (select 1 from #1
where px = a.px + 1 )drop table tb,#,#1/*
-----------
8
*/
create table tb(代码 int, 时间 datetime,jg int)
insert into tb values(1 ,'2008-4-20', 35 )
insert into tb values(1 ,'2008-4-13', 77 )
insert into tb values(1 ,'2008-4-10', 35 )
insert into tb values(1 ,'2008-4-6', 43 )
insert into tb values(1 ,'2008-4-2', 35 )
insert into tb values(1 ,'2008-3-30', 35 )
insert into tb values(2 ,'2008-3-23', 35 )
insert into tb values(1 ,'2008-3-23', 35 )
insert into tb values(2 ,'2008-3-20', 35 )
insert into tb values(1 ,'2008-3-15', 89 )
insert into tb values(1 ,'2008-3-8', 35 )
insert into tb values(1 ,'2008-3-5', 35 )
insert into tb values(1 ,'2008-3-3', 35 )
insert into tb values(1 ,'2008-3-1', 35 )
insert into tb values(1 ,'2008-2-8', 35 )
insert into tb values(1 ,'2008-2-5', 35 )
insert into tb values(1 ,'2008-2-3', 35 )
insert into tb values(1 ,'2008-2-1', 35 )
insert into tb values(2 ,'2008-1-4', 35 )goselect px = identity(int,1,1) ,0 as id,* into # from tbdeclare @i int,@dm int
set @i = 0
update #
set id = @I,@i = @i + (case when 代码= @dm then 0 else 1 end ),@dm = 代码select count(*)
from # where id = 1 and jg = 35
drop table tb,#/*
-----------
4*/
--另外使用了一个临时表,可以不用,就是写起来麻烦些.
create table ta(代码 int, 时间 datetime,jg int)
insert into ta values(1 ,'2008-4-20', 35 )
insert into ta values(1 ,'2008-4-13', 77 )
insert into ta values(1 ,'2008-4-10', 35 )
insert into ta values(1 ,'2008-4-6', 43 )
insert into ta values(1 ,'2008-4-2', 35 )
insert into ta values(1 ,'2008-3-30', 35 )
insert into ta values(2 ,'2008-3-23', 35 )
insert into ta values(1 ,'2008-3-22', 35 )
insert into ta values(2 ,'2008-3-20', 35 )
insert into ta values(1 ,'2008-3-15', 89 )
insert into ta values(1 ,'2008-3-8', 35 )
insert into ta values(1 ,'2008-3-5', 35 )
insert into ta values(1 ,'2008-3-3', 35 )
insert into ta values(1 ,'2008-3-1', 35 )
insert into ta values(1 ,'2008-2-8', 35 )
insert into ta values(1 ,'2008-2-5', 35 )
insert into ta values(1 ,'2008-2-3', 35 )
insert into ta values(1 ,'2008-2-1', 35 )
insert into ta values(2 ,'2008-1-4', 35 )
go--用个临时表。
select * into tb from ta where jg = 35select 次数 = n1.px - n2.px from
(
select px from
(
select * , px = (select count(1) from tb where 时间 > t.时间) + 1 from tb t
) m1,
(
select top 1 * from tb where 时间 < (select top 1 时间 from tb where 时间 < getdate() and 代码 = 1 order by 时间 desc) and 代码 <> 1 order by 时间 desc
) m2
where m1.时间 = m2.时间
) n1,
(
select px from
(
select * , px = (select count(1) from tb where 时间 > t.时间) + 1 from tb t
) m1,
(
select top 1 时间 from tb where 时间 < getdate() and 代码 = 1 order by 时间 desc
) m2
where m1.时间 = m2.时间
) n2 drop table ta,tb/*
次数
-----------
4(所影响的行数为 1 行)
*/
--另外使用了一个临时表,可以不用,就是写起来麻烦些. SQL codecreate table ta(代码 int, 时间 datetime,jg int)
insert into ta values(1 ,'2008-4-20', 35 )
insert into ta values(1 ,'2008-4-13', 77 )
insert into ta values(1 ,'2008-4-10', 35 )
insert into ta values(1 ,'2008-4-6', 43 )
insert into ta values(1 ,'2008-4-2', 35 )
insert into ta values(1 ,'2008-3-30', 35 )
insert into ta values(2 ,'2008-3-23', 35 )
insert into ta values(1 ,'2008-3-22', 35 )
insert into ta values(2 ,'2008-3-20', 35 )
insert into ta values(1 ,'2008-3-15', 89 )
insert into ta values(1 ,'2008-3-8', 35 )
insert into ta values(1 ,'2008-3-5', 35 )
insert into ta values(1 ,'2008-3-3', 35 )
insert into ta values(1 ,'2008-3-1', 35 )
insert into ta values(1 ,'2008-2-8', 35 )
insert into ta values(1 ,'2008-2-5', 35 )
insert into ta values(1 ,'2008-2-3', 35 )
insert into ta values(1 ,'2008-2-1', 35 )
insert into ta values(2 ,'2008-1-4', 35 )
go--用个临时表。
select * into tb from ta where jg = 35select 次数 = n1.px - n2.px from
(
select px from
(
select * , px = (select count(1) from tb where 时间 > t.时间) + 1 from tb t
) m1,
(
select top 1 * from tb where 时间 < (select top 1 时间 from tb where 时间 < getdate() and 代码 = 1 order by 时间 desc) and 代码 <> 1 order by 时间 desc
) m2
where m1.时间 = m2.时间
) n1,
(
select px from
(
select * , px = (select count(1) from tb where 时间 > t.时间) + 1 from tb t
) m1,
(
select top 1 时间 from tb where 时间 < getdate() and 代码 = 1 order by 时间 desc
) m2
where m1.时间 = m2.时间
) n2 drop table ta,tb/*
次数
-----------
4(所影响的行数为 1 行)
*/