[sql server] update中变量的赋值用法 实现分组更新序号

285572001

　　--建立测试环境
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE tb
(
FDate datetime ,
FPeriod int,
FNum int,
FEntryID int
)
GO
INSERT TB ( FDate, FPeriod, FNum )
SELECT '2009-1-1', 1, 1 union all
SELECT '2009-1-1', 1, 1 union all
SELECT '2009-1-1', 1, 1 union all
SELECT '2009-1-1', 1, 1 union all
SELECT '2009-1-3', 1, 2 union all
SELECT '2009-1-3', 1, 2 union all
SELECT '2009-1-3', 1, 2 union all
SELECT '2009-1-4', 1, 4 union all
SELECT '2009-1-4', 1, 4
--查询
--如果update可以
declare @i int,@fnum int
set @i=0
update tb
set FEntryID=@i,
@i=case when @fnum = fnum then @i+1 else 0 end,
@fnum=fnum
go
--
select * from tb
/*
FDate FPeriod FNum FEntryID
----------------------- ----------- ----------- -----------
2009-01-01 00:00:00.000 1 1 0
2009-01-01 00:00:00.000 1 1 1
2009-01-01 00:00:00.000 1 1 2
2009-01-01 00:00:00.000 1 1 3
2009-01-03 00:00:00.000 1 2 0
2009-01-03 00:00:00.000 1 2 1
2009-01-03 00:00:00.000 1 2 2
2009-01-04 00:00:00.000 1 4 0
2009-01-04 00:00:00.000 1 4 1
　　(9 行受影响)
　　*/
　　--如果要查询
--由于sql2000 没有row_number函数，所以要用个临时表
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
GO
CREATE TABLE #t
(
FDate datetime ,
FPeriod int,
FNum int,
FEntryID int identity
)
go
insert #t
select FDate, FPeriod, FNum from tb order by FNum
go
----
select FDate, FPeriod, FNum ,
(select count(1) from #t where fnum=a.fnum and FEntryID<a.FEntryID)FEntryID
from #t a
　　--结果
/*
FDate FPeriod FNum FEntryID
----------------------- ----------- ----------- -----------
2009-01-01 00:00:00.000 1 1 0
2009-01-01 00:00:00.000 1 1 1
2009-01-01 00:00:00.000 1 1 2
2009-01-01 00:00:00.000 1 1 3
2009-01-03 00:00:00.000 1 2 0
2009-01-03 00:00:00.000 1 2 1
2009-01-03 00:00:00.000 1 2 2
2009-01-04 00:00:00.000 1 4 0
2009-01-04 00:00:00.000 1 4 1
　　(9 行受影响)
　　
*/