不行啊! 只排序了一项 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 * from t order by uid desc,class asc ------------结果------------ uid class ----------- ----------- 6 6 5 5 4 4 3 3 2 2 1 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)select * from t order by uid desc,class asc---------------结果----------------------uid class ----------- ----------- 6 6 5 5 4 4 3 3 2 2 1 1
/*if object_id('J') is not null drop table J create table J (uid int,class int)insert J select 1,1 union all select 2,2 union all select 3,3 union all select 4,4 union all select 5,5 union all select 6,6 */select uid,class from (select idd=row_number()over (order by uid ASC),uid from J ) a inner join (select idd=row_number()over (order by class deSC),class from J ) b on a.idd=b.idd愿lz是2005的。
--使用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
select * from table_name order by score asc grade desc
if object_id('qqq') is not null drop table qqq create table qqq (score float,class int)insert qqq select 56,1 union all select 66,1 union all select 77,2 union all select 12,3 union all select 55,4 union all select 25,5 union all select 765,5 union all select 45,5 union all select 98,6 select class,score from qqq group by class,score order by class desc,score
select * from (select * from table order by 成绩) order by 班级 desc
sql 2000 没有找到解决方案
select * from 成绩表 order by 成绩,班级 desc
group by class,scroe order by 成绩 desc,班级 asc;
Select * form 表名 order by 成绩,班级 desc
用order by 成绩 desc,班级
select Classid,score from (select id=row_number()over (order by Classid ASC),Classid from tb ) a inner join (select id=row_number()over (order by score deSC),score from tb ) b on a.id=b.id
select * from score order by banji desc,chengji
select * from tb order by 成绩,班级 DESC
select A.UID,B.CLASS from t as a,t as b order by a.uid desc,b.class asc 试一试会有什么后果 自己想的~~~~~~~
select * from tb order by 成绩 ASC,班级 DESC
/**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=uiduid class ----------- ----------- 1 6 2 5 3 4 4 3 5 2 6 1(6 行受影响)
不好意思! 我再改一下 如果是不连接的id/**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 tselect 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=Ruiduid class ----------- ----------- 1 7 2 6 3 5 4 4 5 3 6 2 8 1(7 行受影响)
不好意思! 我再改一下 如果是不连接的id/**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 tselect 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=Ruiduid 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 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.rowuid class ----------- ----------- 1 6 2 5 3 4 4 3 5 2 6 1
其实我 做过那个例子 是楼主的思路错了select * from table_name order by score asc grade desc 其实答案就是这个
order by 班级 desc,成绩
只排序了一项
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 * from t order by uid desc,class asc
------------结果------------
uid class
----------- -----------
6 6
5 5
4 4
3 3
2 2
1 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)select * from t order by uid desc,class asc---------------结果----------------------uid class
----------- -----------
6 6
5 5
4 4
3 3
2 2
1 1
-------正确结果应该是----------uid class
----------- -----------
1 6
2 5
3 4
4 3
5 2
6 1
----------- -----------
6 1
5 2
4 3
3 4
2 5
1 6
create table J (uid int,class int)insert J
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5 union all
select 6,6
*/select uid,class from
(select idd=row_number()over (order by uid ASC),uid from J ) a inner join
(select idd=row_number()over (order by class deSC),class from J ) b
on a.idd=b.idd愿lz是2005的。
--使用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
if object_id('qqq') is not null drop table qqq
create table qqq (score float,class int)insert qqq
select 56,1 union all
select 66,1 union all
select 77,2 union all
select 12,3 union all
select 55,4 union all
select 25,5 union all
select 765,5 union all
select 45,5 union all
select 98,6
select class,score from qqq
group by class,score
order by class desc,score
没有找到解决方案
(select id=row_number()over (order by Classid ASC),Classid from tb ) a inner join
(select id=row_number()over (order by score deSC),score from tb ) b
on a.id=b.id
from tb
order by 成绩,班级 DESC
试一试会有什么后果 自己想的~~~~~~~
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=uiduid class
----------- -----------
1 6
2 5
3 4
4 3
5 2
6 1(6 行受影响)
我再改一下
如果是不连接的id/**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 tselect 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=Ruiduid class
----------- -----------
1 7
2 6
3 5
4 4
5 3
6 2
8 1(7 行受影响)
我再改一下
如果是不连接的id/**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 tselect 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=Ruiduid 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 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.rowuid class
----------- -----------
1 6
2 5
3 4
4 3
5 2
6 1
其实答案就是这个