67,515
社区成员
发帖
与我相关
我的任务
分享
SQL> SELECT * FROM A;
ID SJ TIMES
--------------------------------------- ----------- ---------------------------------------
10 2009-1-1 3
11 2009-1-2 0
12 2009-1-3 0
13 2009-1-4 0
14 2009-1-5 2
15 2009-1-6 0
16 2009-1-7 0
17 2009-1-8 0
18 2009-1-9 0
19 2009-1-10 0
20 2009-1-5 6
21 2009-1-6 0
22 2009-1-7 0
23 2009-1-8 0
24 2009-1-9 0
25 2009-1-10 0
26 2009-1-9 0
27 2009-1-9 0
28 2009-1-9 1
19 rows selected
SQL>
SQL> SELECT ID, SJ, TIMES
2 FROM (SELECT t.ID,
3 t.SJ,
4 t.TIMES,
5 COUNT(1) OVER(PARTITION BY ZS) C,
6 ROW_NUMBER() OVER(PARTITION BY ZS ORDER BY ROWNUM) RID
7 FROM (SELECT A.*, SUM(TIMES) OVER(ORDER BY ID) ZS FROM A) t)
8 WHERE C < 6 or (C >= 6 AND RID = 1)
9 /
ID SJ TIMES
--------------------------------------- ----------- ---------------------------------------
10 2009-1-1 3
11 2009-1-2 0
12 2009-1-3 0
13 2009-1-4 0
14 2009-1-5 2
20 2009-1-5 6
28 2009-1-9 1
7 rows selected
SQL>
--游标开始
declare @temp_nu int
set @temp_nu=0
declare @id_t int
declare @date_t datetime
declare @times_t int
create table #lin
(
id int,
date datetime,
times int
)
create table #lin_out
(
id int,
date datetime,
times int
)
DECLARE zd CURSOR SCROLL
FOR
select id, date, times from dbo.A
OPEN zd
FETCH RELATIVE 1 FROM zd into @id_t,@date_t,@times_t
WHILE @@FETCH_STATUS = 0
BEGIN
--执行代码部分开始
if @times_t=0
begin
set @temp_nu=@temp_nu+1
insert into #lin(id, date, times) values(@id_t,@date_t,@times_t)
end
if @times_t!=0
begin
if @temp_nu<5
begin
insert into #lin_out(id, date, times)
select id, date, times from #lin
end
set @temp_nu=0
delete from #lin
insert into #lin_out(id, date, times) values(@id_t,@date_t,@times_t)
end
--执行代码部分结束
FETCH RELATIVE 1 FROM zd into @id_t,@date_t,@times_t
END
CLOSE zd
DEALLOCATE zd
if @temp_nu<5
begin
insert into #lin_out(id, date, times)
select id, date, times from #lin
end
select * from #lin_out
drop table #lin
drop table #lin_out