[sql server] update中變數的賦值用法 實現分組更新序號

--建立測試環境
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 行受影響)

*/