declare @tb table ( REC_DATE datetime ) insert @tb select '2004-5-8' union select '2004-5-10' union select '2004-5-11' union select '2004-5-15' union select '2004-5-18' union select '2004-5-21' union select '2004-6-1' union select '2004-6-5' union select '2004-6-7'--查询 select 1+(select count(1) from @tb where REC_DATE<t.REC_DATE )/4 as 'REC_NUM' ,REC_DATE from @tb t order by REC_DATE--结果 /* REC_NUM REC_DATE ----------- ------------------------------------------------------ 1 2004-05-08 00:00:00.000 1 2004-05-10 00:00:00.000 1 2004-05-11 00:00:00.000 1 2004-05-15 00:00:00.000 2 2004-05-18 00:00:00.000 2 2004-05-21 00:00:00.000 2 2004-06-01 00:00:00.000 2 2004-06-05 00:00:00.000 3 2004-06-07 00:00:00.000(所影响的行数为 9 行) */
--生成测试数据 create table #T(REC_NUM int,REC_DATE datetime) insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-10' insert into #T select null,'2004-05-11' insert into #T select null,'2004-05-15' insert into #T select null,'2004-05-18' insert into #T select null,'2004-05-21' insert into #T select null,'2004-06-01' insert into #T select null,'2004-06-05' insert into #T select null,'2004-06-07'--执行更新操作 update a set REC_NUM = isnull((select count(*) from #T where REC_DATE<a.REC_DATE),0)/4+1 from #T a--查看更新结果 select * from #T--输出结果 /* REC_NUM REC_DATE ------- ----------------------- 1 2004-05-08 00:00:00.000 1 2004-05-10 00:00:00.000 1 2004-05-11 00:00:00.000 1 2004-05-15 00:00:00.000 2 2004-05-18 00:00:00.000 2 2004-05-21 00:00:00.000 2 2004-06-01 00:00:00.000 2 2004-06-05 00:00:00.000 3 2004-06-07 00:00:00.000 */
select case when (select count(*)%4 from tb1) =0 then (select case when max(REC_NUM) is null then 1 else max(REC_NUM)+1 end from tb1) else (select case when max(REC_NUM) is null then 1 else max(REC_NUM)end from tb1) end as num from tb1 order by REC_DATE
create table #T(REC_NUM int,REC_DATE datetime) insert into #T select 0,'2004-05-08' insert into #T select 0,'2004-05-08' insert into #T select 0,'2004-05-08' insert into #T select 0,'2004-05-08' insert into #T select 0,'2004-05-08' insert into #T select 0,'2004-05-08' insert into #T select 0,'2004-05-10' insert into #T select 0,'2004-05-11' insert into #T select 0,'2004-05-15' insert into #T select 0,'2004-05-15' insert into #T select 0,'2004-05-15' insert into #T select 0,'2004-05-18' insert into #T select 0,'2004-05-21' insert into #T select 0,'2004-05-21' insert into #T select 0,'2004-05-21' insert into #T select 0,'2004-06-01' insert into #T select 0,'2004-06-05' insert into #T select 0,'2004-06-05' insert into #T select 0,'2004-06-05' insert into #T select 0,'2004-06-07'select SEQ=IDENTITY(int, 1, 1), * into #T2 from #Tupdate #T2 set REC_NUM = (SEQ+3)/4select REC_NUM, REC_DATE from #T2drop table #T, #T2
select 1+(case when (select count(*) from #T where #T.REC_DATE<=T.REC_DATE)%4 =0 then (select count(*) from #T where #T.REC_DATE<=T.REC_DATE)/4-1 else (select count(*) from #T where #T.REC_DATE<=T.REC_DATE)/4 end),T.REC_DATE from #T T order by T.REC_DATE
借助中间临时表中转一下: ----------------------------------------------------------- --生成测试数据 create table #T(REC_NUM int,REC_DATE datetime) insert into #T select null,'2004-05-08' insert into #T select null,'2004-06-07' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-06-15' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-07' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-07' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-07' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-15'--将数据插入临时表,并按日期排序 select * into #T1 from #T order by REC_DATE--在中间临时表上执行更新操作 declare @i int,@j int set @i=1 set @j=0update #T1 set @i = case @j when 4 then @i+1 else @i end, REC_NUM = @i, @j = case @j when 4 then 1 else @j+1 end--清空原表 truncate table #T--将更新结果导回原表 insert into #T select * from #T1--查看执行结果 select * from #T--删除测试数据 drop table #T,#T1
借助辅助列: -------------------------------------------------------- --生成测试数据 create table #T(REC_NUM int,REC_DATE datetime) insert into #T select null,'2004-05-08' insert into #T select null,'2004-06-07' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-06-15' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-07' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-07' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-07' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-15' insert into #T select null,'2004-06-15'--增加自增属性的辅助列 alter table #T add id int identity(1,1) go--执行更新操作 update a set REC_NUM = (select count(*) from #T where REC_DATE<a.REC_DATE or (REC_DATE=a.REC_DATE and id<a.id))/4+1 from #T a--删除辅助列 alter table #T drop column id go--查看更新结果 select * from #T select * from #T order by REC_DATE,REC_NUM--删除测试数据 drop table #T
create table #T(REC_NUM int,REC_DATE datetime) insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-10' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-08' insert into #T select null,'2004-05-11' insert into #T select null,'2004-05-15' insert into #T select null,'2004-05-15' insert into #T select null,'2004-05-15' insert into #T select null,'2004-05-18' insert into #T select null,'2004-05-21' insert into #T select null,'2004-05-21' insert into #T select null,'2004-05-21' insert into #T select null,'2004-06-01' insert into #T select null,'2004-06-05' insert into #T select null,'2004-06-05' insert into #T select null,'2004-06-05' insert into #T select null,'2004-06-07'--处理 select * into # from #t order by REC_DATEdeclare @i int select @i=1 while (select count(*) from # where REC_NUM is null)>0 begin set ROWCOUNT 4 update # set REC_NUM=@i where REC_NUM is null select @i=@i+1 end set rowcount 0delete from #tinsert into #t select * from # --删除临时表 drop table #select * from #t/* 1 2004-05-08 00:00:00.000 1 2004-05-08 00:00:00.000 1 2004-05-08 00:00:00.000 1 2004-05-08 00:00:00.000 2 2004-05-08 00:00:00.000 2 2004-05-08 00:00:00.000 2 2004-05-10 00:00:00.000 2 2004-05-11 00:00:00.000 3 2004-05-15 00:00:00.000 3 2004-05-15 00:00:00.000 3 2004-05-15 00:00:00.000 3 2004-05-18 00:00:00.000 4 2004-05-21 00:00:00.000 4 2004-05-21 00:00:00.000 4 2004-05-21 00:00:00.000 4 2004-06-01 00:00:00.000 5 2004-06-05 00:00:00.000 5 2004-06-05 00:00:00.000 5 2004-06-05 00:00:00.000 5 2004-06-07 00:00:00.000*/
(
REC_DATE datetime
)
insert @tb
select '2004-5-8' union
select '2004-5-10' union
select '2004-5-11' union
select '2004-5-15' union
select '2004-5-18' union
select '2004-5-21' union
select '2004-6-1' union
select '2004-6-5' union
select '2004-6-7'--查询
select 1+(select count(1) from @tb where REC_DATE<t.REC_DATE )/4 as 'REC_NUM'
,REC_DATE
from @tb t
order by REC_DATE--结果
/*
REC_NUM REC_DATE
----------- ------------------------------------------------------
1 2004-05-08 00:00:00.000
1 2004-05-10 00:00:00.000
1 2004-05-11 00:00:00.000
1 2004-05-15 00:00:00.000
2 2004-05-18 00:00:00.000
2 2004-05-21 00:00:00.000
2 2004-06-01 00:00:00.000
2 2004-06-05 00:00:00.000
3 2004-06-07 00:00:00.000(所影响的行数为 9 行)
*/
create table #T(REC_NUM int,REC_DATE datetime)
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-10'
insert into #T select null,'2004-05-11'
insert into #T select null,'2004-05-15'
insert into #T select null,'2004-05-18'
insert into #T select null,'2004-05-21'
insert into #T select null,'2004-06-01'
insert into #T select null,'2004-06-05'
insert into #T select null,'2004-06-07'--执行更新操作
update a
set
REC_NUM = isnull((select count(*) from #T where REC_DATE<a.REC_DATE),0)/4+1
from
#T a--查看更新结果
select * from #T--输出结果
/*
REC_NUM REC_DATE
------- -----------------------
1 2004-05-08 00:00:00.000
1 2004-05-10 00:00:00.000
1 2004-05-11 00:00:00.000
1 2004-05-15 00:00:00.000
2 2004-05-18 00:00:00.000
2 2004-05-21 00:00:00.000
2 2004-06-01 00:00:00.000
2 2004-06-05 00:00:00.000
3 2004-06-07 00:00:00.000
*/
then (select case when max(REC_NUM) is null then 1 else max(REC_NUM)+1 end from tb1)
else (select case when max(REC_NUM) is null then 1 else max(REC_NUM)end from tb1) end as num
from tb1
order by REC_DATE
例如:原表
REC_NUM REC_DATE
null 2004-5-8
null 2004-6-7
null 2004-5-8
null 2004-5-8
null 2004-5-8
null 2004-5-8
null 2004-5-8
null 2004-6-15
null 2004-5-8
null 2004-5-8
null 2004-6-15
null 2004-6-7
null 2004-6-15
null 2004-6-7
null 2004-6-15
null 2004-6-15
null 2004-6-7
null 2004-6-15
null 2004-6-15
null 2004-6-15
排序后
REC_NUM REC_DATE
null 2004-5-8
null 2004-5-8
null 2004-5-8
null 2004-5-8
null 2004-5-8
null 2004-5-8
null 2004-5-8
null 2004-5-8
null 2004-6-7
null 2004-6-7
null 2004-6-7
null 2004-6-7
null 2004-6-15
null 2004-6-15
null 2004-6-15
null 2004-6-15
null 2004-6-15
null 2004-6-15
null 2004-6-15
null 2004-6-15
更新后的按REC_DATE排序的表
REC_NUM REC_DATE
1 2004-5-8
1 2004-5-8
1 2004-5-8
1 2004-5-8
2 2004-5-8
2 2004-5-8
2 2004-5-8
2 2004-5-8
3 2004-6-7
3 2004-6-7
3 2004-6-7
3 2004-6-7
4 2004-6-15
4 2004-6-15
4 2004-6-15
4 2004-6-15
5 2004-6-15
5 2004-6-15
5 2004-6-15
5 2004-6-15
insert into #T select 0,'2004-05-08'
insert into #T select 0,'2004-05-08'
insert into #T select 0,'2004-05-08'
insert into #T select 0,'2004-05-08'
insert into #T select 0,'2004-05-08'
insert into #T select 0,'2004-05-08'
insert into #T select 0,'2004-05-10'
insert into #T select 0,'2004-05-11'
insert into #T select 0,'2004-05-15'
insert into #T select 0,'2004-05-15'
insert into #T select 0,'2004-05-15'
insert into #T select 0,'2004-05-18'
insert into #T select 0,'2004-05-21'
insert into #T select 0,'2004-05-21'
insert into #T select 0,'2004-05-21'
insert into #T select 0,'2004-06-01'
insert into #T select 0,'2004-06-05'
insert into #T select 0,'2004-06-05'
insert into #T select 0,'2004-06-05'
insert into #T select 0,'2004-06-07'select SEQ=IDENTITY(int, 1, 1), * into #T2 from #Tupdate #T2 set REC_NUM = (SEQ+3)/4select REC_NUM, REC_DATE from #T2drop table #T, #T2
(select count(*) from #T where #T.REC_DATE<=T.REC_DATE)/4-1
else
(select count(*) from #T where #T.REC_DATE<=T.REC_DATE)/4
end),T.REC_DATE from #T T order by T.REC_DATE
原表
REC_NUM REC_DATE
null 2004-5-8
null 2004-6-7
null 2004-5-8
null 2004-5-18
null 2004-5-7
null 2004-5-24
null 2004-5-3
null 2004-6-15
null 2004-6-6我想得到的结果REC_NUM REC_DATE
1 2004-5-8
2 2004-6-7
1 2004-5-8
2 2004-5-18
1 2004-5-7
2 2004-5-24
1 2004-5-3
3 2004-6-15
2 2004-6-6大家帮忙!
-----------------------------------------------------------
--生成测试数据
create table #T(REC_NUM int,REC_DATE datetime)
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-06-07'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-07'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-07'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-07'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-15'--将数据插入临时表,并按日期排序
select * into #T1 from #T order by REC_DATE--在中间临时表上执行更新操作
declare @i int,@j int
set @i=1
set @j=0update #T1
set
@i = case @j when 4 then @i+1 else @i end,
REC_NUM = @i,
@j = case @j when 4 then 1 else @j+1 end--清空原表
truncate table #T--将更新结果导回原表
insert into #T select * from #T1--查看执行结果
select * from #T--删除测试数据
drop table #T,#T1
--------------------------------------------------------
--生成测试数据
create table #T(REC_NUM int,REC_DATE datetime)
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-06-07'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-07'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-07'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-07'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-15'
insert into #T select null,'2004-06-15'--增加自增属性的辅助列
alter table #T add id int identity(1,1)
go--执行更新操作
update a
set
REC_NUM = (select count(*) from #T where REC_DATE<a.REC_DATE or (REC_DATE=a.REC_DATE and id<a.id))/4+1
from
#T a--删除辅助列
alter table #T drop column id
go--查看更新结果
select * from #T
select * from #T order by REC_DATE,REC_NUM--删除测试数据
drop table #T
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-10'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-08'
insert into #T select null,'2004-05-11'
insert into #T select null,'2004-05-15'
insert into #T select null,'2004-05-15'
insert into #T select null,'2004-05-15'
insert into #T select null,'2004-05-18'
insert into #T select null,'2004-05-21'
insert into #T select null,'2004-05-21'
insert into #T select null,'2004-05-21'
insert into #T select null,'2004-06-01'
insert into #T select null,'2004-06-05'
insert into #T select null,'2004-06-05'
insert into #T select null,'2004-06-05'
insert into #T select null,'2004-06-07'--处理
select * into # from #t order by REC_DATEdeclare @i int
select @i=1
while (select count(*) from # where REC_NUM is null)>0
begin
set ROWCOUNT 4
update # set REC_NUM=@i where REC_NUM is null
select @i=@i+1
end
set rowcount 0delete from #tinsert into #t select * from #
--删除临时表
drop table #select * from #t/*
1 2004-05-08 00:00:00.000
1 2004-05-08 00:00:00.000
1 2004-05-08 00:00:00.000
1 2004-05-08 00:00:00.000
2 2004-05-08 00:00:00.000
2 2004-05-08 00:00:00.000
2 2004-05-10 00:00:00.000
2 2004-05-11 00:00:00.000
3 2004-05-15 00:00:00.000
3 2004-05-15 00:00:00.000
3 2004-05-15 00:00:00.000
3 2004-05-18 00:00:00.000
4 2004-05-21 00:00:00.000
4 2004-05-21 00:00:00.000
4 2004-05-21 00:00:00.000
4 2004-06-01 00:00:00.000
5 2004-06-05 00:00:00.000
5 2004-06-05 00:00:00.000
5 2004-06-05 00:00:00.000
5 2004-06-07 00:00:00.000*/