更新排名 ，写法，求高手！
表A
列1 列2 列3
A NULL 5
A NULL 3
B NULL 9
B NULL 16
B NULL 10
更新为
列1 列2 列3
A 2 5
A 1 3
B 1 9
B 3 16
B 2 10
按列1的分类 更新 列2 （排名列）。
求UPDATE。。
另需要考虑排名 变动后存在的 排名问题。
[解决办法]
- SQL code
--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([列1] varchar(1),[列2] int,[列3] int)insert [tbl]select 'A',null,5 union allselect 'A',null,3 union allselect 'B',null,9 union allselect 'B',null,16 union allselect 'B',null,10;with tas(select *,ROW_NUMBER()over(partition by [列1] order by [列3] asc) as id from tbl)update [tbl] set [列2]=id from twhere t.列1=tbl.列1 and t.列3=tbl.列3select * from tbl/*列1 列2 列3A 2 5A 1 3B 1 9B 3 16B 2 10*/
[解决办法]
- SQL code
--考虑重复排名吗？表应该有主键id吧！;with ach as( select *,rid=row_number() over (partition by col1 order by col3,id) from tb)update aset a.col2 = b.ridfrom tb a join ach b on a.id = b.id--这个写法是05的，2000的如下：select *,rid=identity(int,1,1) into #tbfrom tborder by col1,col3,idupdate aset a.col2 = (select count(*) from #tb where col1=a.col1 and rid<=a.rid)from tb a
[解决办法]
[解决办法]
- SQL code
use tempdbgoif object_id('tempdb..#') Is not null Drop Table #create table #(col1 nvarchar(50),col2 int,col3 int)insert into # ( col1, col3 ) Select 'A',5 Union All Select 'A',3 Union All Select 'B',9 Union All Select 'B',16 Union All Select 'B',10Update a Set a.col2=b.seq From # As a Inner Join ( Select x.col1,x.col3,count(1) As seq from # As x left Outer Join # As y On y.col1=x.col1 And y.col3<=x.col3 Group By x.col1,x.col3 ) As b On b.col1=a.col1 And b.col3=a.col3Select * from #
[解决办法]