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