SQL表操作
我有这样的数据：
CREATE TABLE #tmp
(
staff NVARCHAR(200),
date DATETIME,
ID INT,
Hour INT
)

INSERT INTO #tmp 
SELECT '10003','2012-08-05 00:00:00.000',2,0  
UNION 
SELECT '10003','2012-08-06 00:00:00.000',0,8
UNION 
SELECT '10003','2012-08-07 00:00:00.000',0,8  
UNION 
SELECT '10003','2012-08-08 00:00:00.000',0,8  
UNION 
SELECT '10003','2012-08-11 00:00:00.000',3,0  
UNION 
SELECT '10003','2012-08-12 00:00:00.000',4,0  
UNION 
SELECT '10003','2012-08-13 00:00:00.000',0,8  
UNION 
SELECT '10003','2012-08-17 00:00:00.000',0,8  
UNION 
SELECT '10003','2012-08-18 00:00:00.000',5,0  

SELECT * FROM #tmp

我想对 ID 这一列操作，结果变成这样：

10003 2012-08-05 00:00:00.000 2 0
10003 2012-08-06 00:00:00.000 2 8
10003 2012-08-07 00:00:00.000 2 8
10003 2012-08-08 00:00:00.000 2 8
10003 2012-08-11 00:00:00.000 3 0
10003 2012-08-12 00:00:00.000 4 0
10003 2012-08-13 00:00:00.000 4 8
10003 2012-08-17 00:00:00.000 4 8
10003 2012-08-18 00:00:00.000 5 0

注意看第三列的规律

------解决方案--------------------
declare @id int =0,@staff varchar(20)=null
update #tmp set id=@id,@staff=case when @staff=null then staff
when @staff<>staff then staff else @staff end,
 @id=case when id>0 and (staff=@staff or @staff is null) then id else @id end
------解决方案--------------------

SQL code

--select
SELECT *,(SELECT  MAX(id) FROM #tmp WHERE date<=a.date   )   FROM #tmp AS a
--update
UPDATE a  SET  id=(SELECT  MAX(id) FROM #tmp WHERE date<=a.date)   FROM #tmp AS a
SELECT *FROM #tmp