UPDATE TB SET SORTID=(SELECT COUNT(*) FROM TB WHERE userName=T.userName AND TIME<=T.TIME) FROM TB T?
update userInfo set sortId=@sortId where userName=@userName
create table tb(userName nvarchar(1) )insert tb select 'a' union select 'b' union select 'c' union select 'd' union select 'e' union select 'e'alter table tb add sortid int identity(1,1) select * from tbuserName sortid -------- ----------- a 1 b 2 c 3 d 4 e 5(5 行受影响) drop table tb
我要的效果:原始数据: userName sortId c null a null b null跟新后的数据: userName sortId c 3 a 1 b 2
UPDATE TB SET SORTID=(SELECT COUNT(*) FROM TB WHERE userName<=T.userName) FROM TB T ?
create TABLE #t(username VARCHAR(20)) insert #t VALUES('c') insert #t VALUES('a') insert #t VALUES('b')ALTER TABLE #t ADD sortid int UPDATE #t SET sortid=(SELECT COUNT(1) FROM #t WHERE username <=a.username) FROM #t a
SELECT * FROM #t /* username sortid -------------------- ----------- c 3 a 1 b 2 */
create table tb(userName nvarchar(1) )insert tb select
'a' union select
'b' union select
'c' union select
'd' union select
'e' union select
'e'alter table tb add sortid int identity(1,1)
select * from tbuserName sortid
-------- -----------
a 1
b 2
c 3
d 4
e 5(5 行受影响)
drop table tb
c null
a null
b null跟新后的数据: userName sortId
c 3
a 1
b 2
?
create TABLE #t(username VARCHAR(20))
insert #t VALUES('c')
insert #t VALUES('a')
insert #t VALUES('b')ALTER TABLE #t ADD sortid int
UPDATE #t SET sortid=(SELECT COUNT(1) FROM #t WHERE username <=a.username)
FROM #t a
SELECT * FROM #t
/*
username sortid
-------------------- -----------
c 3
a 1
b 2
*/