我有这样的数据：
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
--selectSELECT *,(SELECT MAX(id) FROM #tmp WHERE date<=a.date ) FROM #tmp AS a--updateUPDATE a SET id=(SELECT MAX(id) FROM #tmp WHERE date<=a.date) FROM #tmp AS aSELECT *FROM #tmp