SQL表操作
我有这样的数据：
CREATE TABLE #tmp
(
staffNVARCHAR(200),
dateDATETIME,
IDINT,
HourINT
)

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 这一列操作，结果变成这样：

100032012-08-05 00:00:00.00020
100032012-08-06 00:00:00.00028
100032012-08-07 00:00:00.00028
100032012-08-08 00:00:00.00028
100032012-08-11 00:00:00.00030
100032012-08-12 00:00:00.00040
100032012-08-13 00:00:00.00048
100032012-08-17 00:00:00.00048
100032012-08-18 00:00:00.00050

注意看第三列的规律

[解决办法]
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