用一种很笨的方法 用游标做 declare stra varchar(20),inta int inta=1 declare int cursor for select 代码 from 表1open int fetch first from int into stra while @@fetch_status=1 begin if stra= 1 then inta = inta + 1 fetch next from int into stra else close int deallocate int end if end 最后inta 的值就是1连续出现的次数
--> 测试数据: #1 if object_id('tempdb.dbo.#1') is not null drop table #1 create table #1 (代码 int) insert into #1 select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 2 union all select 1 union all select 2if object_id('tempdb.dbo.#') is not null drop table # select id=identity(int,1,1),* into # from #1select count(*) from # a left join # b on a.id=b.id-1 and a.代码=b.代码 left join # c on a.id=c.id+1 and a.代码=c.代码 where a.代码=1 and (b.id is not null or c.id is not null)/* 6 */
create table tb(代码 int, 时间 datetime) insert into tb values(1 ,'2008-4-20' ) insert into tb values(1 ,'2008-4-13' ) insert into tb values(1 ,'2008-4-10' ) insert into tb values(1 ,'2008-4-6' ) insert into tb values(1 ,'2008-4-2' ) insert into tb values(1 ,'2008-3-30' ) insert into tb values(2 ,'2008-3-23' ) insert into tb values(1 ,'2008-3-20' ) insert into tb values(1 ,'2008-3-15' ) insert into tb values(1 ,'2008-3-8' ) insert into tb values(2 ,'2008-3-5' ) insert into tb values(1 ,'2008-3-2') insert into tb values(1 ,'2008-3-1') goselect 次数 = 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.时间 ) n2drop table tb/* 次数 ----------- 6(所影响的行数为 1 行) */
create table tmptb ( code int, dt datetime ) go insert into tmptb values (1, '2008-4-20') insert into tmptb values (1, '2008-4-13') insert into tmptb values (1, '2008-4-10') insert into tmptb values (1, '2008-4-6') insert into tmptb values (1, '2008-4-2') insert into tmptb values (1, '2008-3-30') insert into tmptb values (2, '2008-3-23') insert into tmptb values (1, '2008-3-20') insert into tmptb values (2, '2008-3-15') insert into tmptb values (2, '2008-3-8') insert into tmptb values (2, '2008-3-5') go-- 挑出“代码连续出现”的记录 select code, dt from tmptb a where exists (select 1 from tmptb b where b.code=a.code and b.dt>a.dt and not exists (select 1 from tmptb b0 where b0.code!=b.code and b0.dt>a.dt and b0.dt<b.dt ) ) or exists (select 1 from tmptb b where b.code=a.code and b.dt<a.dt and not exists (select 1 from tmptb b0 where b0.code!=b.code and b0.dt<a.dt and b0.dt>b.dt ) ) -- code dt -- 1 2008-04-20 00:00:00.000 -- 1 2008-04-13 00:00:00.000 -- 1 2008-04-10 00:00:00.000 -- 1 2008-04-06 00:00:00.000 -- 1 2008-04-02 00:00:00.000 -- 1 2008-03-30 00:00:00.000 -- 2 2008-03-15 00:00:00.000 -- 2 2008-03-08 00:00:00.000 -- 2 2008-03-05 00:00:00.000 go-- 统计: select code, count(1) from tmptb a where exists (select 1 from tmptb b where b.code=a.code and b.dt>a.dt and not exists (select 1 from tmptb b0 where b0.code!=b.code and b0.dt>a.dt and b0.dt<b.dt ) ) or exists (select 1 from tmptb b where b.code=a.code and b.dt<a.dt and not exists (select 1 from tmptb b0 where b0.code!=b.code and b0.dt<a.dt and b0.dt>b.dt ) ) group by code -- code -- 1 6 -- 2 3 -- go drop table tmptb go
declare stra varchar(20),inta int
inta=1
declare int cursor
for
select 代码 from 表1open int
fetch first from int into stra
while @@fetch_status=1
begin
if stra= 1 then
inta = inta + 1
fetch next from int into stra
else
close int
deallocate int
end if
end
最后inta 的值就是1连续出现的次数
if object_id('tempdb.dbo.#1') is not null drop table #1
create table #1 (代码 int)
insert into #1
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 1 union all
select 2 union all
select 1 union all
select 2if object_id('tempdb.dbo.#') is not null drop table #
select id=identity(int,1,1),* into # from #1select count(*) from # a
left join # b on a.id=b.id-1 and a.代码=b.代码
left join # c on a.id=c.id+1 and a.代码=c.代码
where a.代码=1 and (b.id is not null or c.id is not null)/*
6
*/
insert into tb values(1 ,'2008-4-20' )
insert into tb values(1 ,'2008-4-13' )
insert into tb values(1 ,'2008-4-10' )
insert into tb values(1 ,'2008-4-6' )
insert into tb values(1 ,'2008-4-2' )
insert into tb values(1 ,'2008-3-30' )
insert into tb values(2 ,'2008-3-23' )
insert into tb values(1 ,'2008-3-20' )
insert into tb values(1 ,'2008-3-15' )
insert into tb values(1 ,'2008-3-8' )
insert into tb values(2 ,'2008-3-5' )
insert into tb values(1 ,'2008-3-2')
insert into tb values(1 ,'2008-3-1')
goselect 次数 = 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.时间
) n2drop table tb/*
次数
-----------
6(所影响的行数为 1 行)
*/
go
insert into tmptb values (1, '2008-4-20')
insert into tmptb values (1, '2008-4-13')
insert into tmptb values (1, '2008-4-10')
insert into tmptb values (1, '2008-4-6')
insert into tmptb values (1, '2008-4-2')
insert into tmptb values (1, '2008-3-30')
insert into tmptb values (2, '2008-3-23')
insert into tmptb values (1, '2008-3-20')
insert into tmptb values (2, '2008-3-15')
insert into tmptb values (2, '2008-3-8')
insert into tmptb values (2, '2008-3-5')
go-- 挑出“代码连续出现”的记录
select code, dt
from tmptb a
where exists (select 1 from tmptb b
where b.code=a.code
and b.dt>a.dt
and not exists (select 1 from tmptb b0
where b0.code!=b.code
and b0.dt>a.dt
and b0.dt<b.dt
)
) or exists (select 1 from tmptb b
where b.code=a.code
and b.dt<a.dt
and not exists (select 1 from tmptb b0
where b0.code!=b.code
and b0.dt<a.dt
and b0.dt>b.dt
)
)
-- code dt
-- 1 2008-04-20 00:00:00.000
-- 1 2008-04-13 00:00:00.000
-- 1 2008-04-10 00:00:00.000
-- 1 2008-04-06 00:00:00.000
-- 1 2008-04-02 00:00:00.000
-- 1 2008-03-30 00:00:00.000
-- 2 2008-03-15 00:00:00.000
-- 2 2008-03-08 00:00:00.000
-- 2 2008-03-05 00:00:00.000
go-- 统计:
select code, count(1)
from tmptb a
where exists (select 1 from tmptb b
where b.code=a.code
and b.dt>a.dt
and not exists (select 1 from tmptb b0
where b0.code!=b.code
and b0.dt>a.dt
and b0.dt<b.dt
)
) or exists (select 1 from tmptb b
where b.code=a.code
and b.dt<a.dt
and not exists (select 1 from tmptb b0
where b0.code!=b.code
and b0.dt<a.dt
and b0.dt>b.dt
)
)
group by code
-- code
-- 1 6
-- 2 3
--
go
drop table tmptb
go