分组排序SQL语句

[MIS_ECSM]

表 tbl
name class totol
a1 1 4
a2 2 6
a3 1 4
a4 1 3
a5 3 5
a6 2 1

查询结果显示
no 序号 name class totol
1 1 a6 2 1
2 1 a4 1 3
3 2 a1 1 4
4 3 a3 1 4
5 1 a5 3 5
6 2 a2 2 6

也就是说 序号 列是以class分组后的分别排名
也就是相当于一个学校的所有学生考试成绩
no是年级排名，序号是班级排名

[$扫地僧$]

select * from tbl order by totol

[$扫地僧$]

看错了!

create table tbl
(
name varchar(10),
class int,
totol int
)



insert tbl select 'a1',1,4
insert tbl select 'a2',2,6
insert tbl select 'a3',1,4
insert tbl select 'a4',1,3
insert tbl select 'a5',3,5
insert tbl select 'a6',2,1


select
(select count(1) from tbl where totol<=T.totol) as no,
(select count(1) from tbl where class=T.class and totol<=T.totol) as 序号,
T.name,T.class,T.totol
from tbl T
order by totol

[aniude]

学习

[aniude]

/*
no 序号 name class totol
----------- ----------- ---------- ----------- -----------
1 1 a6 2 1
2 1 a4 1 3
4 3 a3 1 4
4 3 a1 1 4
5 1 a5 3 5
6 2 a2 2 6

（所影响的行数为 6 行）
*/
结果错误

[$扫地僧$]

晕啦!
非要搞得这么麻烦!!


create table tbl
(
name varchar(10),
class int,
totol int
)



insert tbl select 'a1',1,4
insert tbl select 'a2',2,6
insert tbl select 'a3',1,4
insert tbl select 'a4',1,3
insert tbl select 'a5',3,5
insert tbl select 'a6',2,1

select identity(int,1,1) as id,* into # from tbl order by totol


select
(select count(1)+1 from # where totol<=T.totol and id<t.id) as no,
(select count(1)+1 from # where class=T.class and totol<=T.totol and id<t.id) as 序号,
T.name,T.class,T.totol
from # T
order by totol