某个表T 查询后用id 排序后的结果如下:
id(自动ID) Sname Scount
100 aa 1
101 ca 3
102 ba 6
103 da 3用id 排序后的结果要修改,想一次改成如下结果。
100 aa 1
101 ba 6
102 ca 3
103 da 3
id(自动ID) Sname Scount
100 aa 1
101 ca 3
102 ba 6
103 da 3用id 排序后的结果要修改,想一次改成如下结果。
100 aa 1
101 ba 6
102 ca 3
103 da 3
select 101,'ca',3 union all
select 102,'ba',6 union all
select 103,'da',3
select identity(int,100,1) as id,sname,scount into #
from @t
order by snameselect * from #drop table #/*id sname scount
----------- ----- -----------
100 aa 1
101 ba 6
102 ca 3
103 da 3*/
100 aa 1
101 ba 6
102 ca 3
103 da 3
次序。把表的真正记录要改变。
最后要
select * from t order by id 语句实现
100 aa 1
101 ba 6
102 ca 3
103 da 3
效果。
select identity(int,100,1) as id,sname,scount into #
from @t
order by snameupdate tablename
set id =( select id from # where sname = a.sname and scount = a.scount)
from tablename a
SET IDENTITY_INSERT tablename OFF
select * from tablename order by id
drop table #
谢谢您!可是这个程序过不了。
不知道在那里错误!