mssql数据排序的问题
我有两个表T1(aid,aname),T2(bid,bname)
aid与bid关联,aid不会重复相同数据,bid可以重复相同数据.
我现在T1数据如下
(1,'a')
(3,'b')
(10,'c')
我现在T2数据如下
(1,'aa')
(1,'aa')
(3,'b')
(10,'c')
(10,'c')
---------------------------------------
我要求数据T1的aid数据从1顺序排序,也就是1,2,3.
那么T2对应的数据bid也要变成1,2,3,也就是T2表的数据如下
(1,'aa')
(1,'aa')
(2,'b')
(3,'c')
(3,'c')
[解决办法]
create table T1(aid int, aname varchar(5))create table T2(bid int, bname varchar(5))insert into T1values(1,'a'),(3,'b'),(10,'c')insert into T2values(1,'aa'),(1,'aa'),(3,'b'),(10,'c'),(10,'c')with t as( select aid,aname, row_number() over(order by aid) rn from t1)update bset b.bid=a.rnfrom T2 binner join t a on b.bid=a.aid;with t as( select aid,aname, row_number() over(order by aid) rn from T1)update aset a.aid=b.rnfrom T1 ainner join t b on a.aid=b.aid;select * from T1/*aid aname----------- -----1 a2 b3 c(3 row(s) affected)*/select * from T2/*bid bname----------- -----1 aa1 aa2 b3 c3 c(5 row(s) affected)*/
