34,838
社区成员




create table t(uid int,class int)
go
insert t values(1,1)
insert t values(2,2)
insert t values(3,3)
insert t values(4,4)
insert t values(5,5)
insert t values(6,6)
select a.uid,b.class from
(select uid,row_number()over(order by uid) as row from t) a,
(select class,row_number()over(order by class desc) as row from t) b
where a.row=b.row
uid class
----------- -----------
1 6
2 5
3 4
4 3
5 2
6 1
/**create table t(uid int,class int)
go
insert t values(1,1)
insert t values(2,2)
insert t values(3,3)
insert t values(4,4)
insert t values(5,5)
insert t values(6,6)
insert t values(8,7) --多插入一行不连续的id
**/
--select class,rank() over(order by class desc) as Rclass from t
--select uid,rank() over(order by uid asc) as Ruid from t
select b.uid,a.class from
(select class,rank() over(order by class desc) as Rclass from t) as a
inner join
(select uid,rank() over(order by uid asc) as Ruid from t) as b
on a.Rclass=Ruid
uid class
----------- -----------
1 7
2 6
3 5
4 4
5 3
6 2
8 1
(7 行受影响)
/**create table t(uid int,class int)
go
insert t values(1,1)
insert t values(2,2)
insert t values(3,3)
insert t values(4,4)
insert t values(5,5)
insert t values(6,6)
insert t values(8,7) --多插入一行不连续的id
**/
--select class,rank() over(order by class desc) as Rclass from t
--select uid,rank() over(order by uid asc) as Ruid from t
select b.uid,a.class from
(select class,rank() over(order by class desc) as Rclass from t) as a
inner join
(select uid,rank() over(order by uid asc) as Ruid from t) as b
on a.Rclass=Ruid
uid class
----------- -----------
1 7
2 6
3 5
4 4
5 3
6 2
8 1
(7 行受影响)
/**create table t(uid int,class int)
go
insert t values(1,1)
insert t values(2,2)
insert t values(3,3)
insert t values(4,4)
insert t values(5,5)
insert t values(6,6)
**/
select b.uid,a.class from
(select class,rank() over(order by class desc) as Rclass from t) as a
inner join
(select uid,rank() over(order by uid asc) as Ruid from t) as b
on a.Rclass=uid
uid class
----------- -----------
1 6
2 5
3 4
4 3
5 2
6 1
(6 行受影响)
--使用rank函数,在重值的情况下,不会同名,连续性。
select uid,class from
(select id=Rank()over (order by uid ASC),uid from tb ) a inner join
(select id=rank()over (order by class deSC),class from tb ) b
on a.id=b.id