CREATE TABLE #Tmp (Thid Char(1),other int)
INSERT INTO #Tmp VALUES ('a',1)
INSERT INTO #Tmp VALUES ('a',3)
INSERT INTO #Tmp VALUES ('c',2)
INSERT INTO #Tmp VALUES ('b',0)
INSERT INTO #Tmp VALUES ('b',0)
INSERT INTO #Tmp VALUES ('b',0)
INSERT INTO #Tmp VALUES ('c',2)
--加索引,保証相同的組在同一位置上
CREATE NONCLUSTERED INDEX Idx_Test ON #tmp (Thid,other ASC)
SELECT* FROM #Tmp
DECLARE @Thid CHAR(1)
,@Other INT
SET @Other=0
UPDATE tmp
SET
@Other=CASE
WHEN @Thid=Thid THEN @Other+1 ELSE Other
END
,@Thid=CASE
WHEN COALESCE(@Thid,'')=Thid THEN @THid ELSE Thid
END
,Other=@Other
FROM #tmp Tmp
SELECT* FROM #Tmp
DROP TABLE #tmp
INSERT INTO #Tmp VALUES ('a',1)
INSERT INTO #Tmp VALUES ('a',3)
INSERT INTO #Tmp VALUES ('c',2)
INSERT INTO #Tmp VALUES ('b',0)
INSERT INTO #Tmp VALUES ('b',0)
INSERT INTO #Tmp VALUES ('b',0)
INSERT INTO #Tmp VALUES ('c',2)
--加索引,保証相同的組在同一位置上
CREATE NONCLUSTERED INDEX Idx_Test ON #tmp (Thid,other ASC)
SELECT* FROM #Tmp
DECLARE @Thid CHAR(1)
,@Other INT
SET @Other=0
UPDATE tmp
SET
@Other=CASE
WHEN @Thid=Thid THEN @Other+1 ELSE Other
END
,@Thid=CASE
WHEN COALESCE(@Thid,'')=Thid THEN @THid ELSE Thid
END
,Other=@Other
FROM #tmp Tmp
SELECT* FROM #Tmp
DROP TABLE #tmp
a ...
a ...
b ..
b ...
c ..
c...
c...将得到
a 1
a 2
b 1
b 2
c 1
c 2
c 3update语句执行时,第一行,因为@thid未赋值,所以@thid!=当前记录的thid,@thor则为0+1即1,@thid='a',则other列被更新为1
第二行,因为上一行得到@thid为a,则@thor继续加1, other列被更新为2
第三行,@thid=a,当前行thid=b,所以@thor初始化,成为0 (因为取b组的第一行的other列值),other被更新为0
第四行,因为@thid不变,@thor继续加1,得到other更新为1...实际上的意思就是用一个@thid记录上一行的组号,如果组同(当前行与上一行同组),则@thor加1,
若当前行的thid与@thid不同(即当前行与上一行不同组),则@thor初始化
然后,再用得到的@thor去更新other列。
@Other=CASE
WHEN @Thid=Thid THEN @Other+1 ELSE Other
END
,@Thid=CASE
WHEN COALESCE(@Thid,'')=Thid THEN @THid ELSE Thid
END
,Other=@Other 就是说如果当前行变组了,那么thor为当前组的第一行的other值,当前组的后面的行的other值依次在当前物的other值上加1这样的更新让人有点费解,为什么第一个组要以1初始化,而后面的其它组则是以组中第一行记录的other值来初始化。
如果写成下面怕样,那么所有组的更新规则就都一样了
SET
@Other=CASE
WHEN @Thid=Thid THEN @Other+1 ELSE 1
END
,@Thid=CASE
WHEN COALESCE(@Thid,'')=Thid THEN @THid ELSE Thid
END
,Other=@Other
为什么上面的结果是
a 1
a 2
b 0
b 1
b 2
c 2
c 2
你的语句意思是第一个开始,同Thid 则增1,不同则取第1个,因为C没有在一起,所以取了两次第一个值,都是2
不过它的效率是很高的.比较有保证的做法是,在更新之前,按Table的分组字段排序Insert到一个Temptable中,
然后再做更新
或者是建一个Clustered Index(这种方法我觉得还是不太安全)EX:
(1). CREATE CLUSTERED INDEX Idx_Test ON #tmp (Thid,other ASC)
DECLARE @Thid VARCHAR(2)
,@Other INT ,@i INT
UPDATE tmp
SET @i = 1 + @i,
@Thid=@Thid+Thid,
@Other = Other = CASE WHEN Thid = Left(@Thid,Len(@Thid)/ @i) THEN @Other + 1 ELSE 1 END,
@i = CASE WHEN Thid = Left(@Thid,Len(@Thid)/ @i) THEN @i ELSE 1 END,
@Thid =Thid,
@i= CASE WHEN @i=2 THEN CASE WHEN Thid = @Thid THEN 1 ELSE @i END ELSE 1 END
FROM #tmp tmp(2). SELECT IDENTITY(int,1,1) as ROW,* INTO #t FROM #Tmp
UPDATE #T
SET Other=(SELECT COUNT(*) FROM #t WHERE Row<=a.Row AND Thid=a.Thid)
FROM #t a