不知道你的这个具体排名是怎么设定的:create table class( number varchar(2), name varchar(10), cno varchar(2), avgscore int )insert into class values('01','tom','01',89) insert into class values('02','jacy','01',78) insert into class values('03','ekin','02',69) insert into class values('04','andy','02',69)select count(1) from class where avgscore>=( select avgscore from class where number='01' and cno='01' ) and cno='01'
declare @tb table ( [学号] varchar(10), [姓名] varchar(10), [班级] varchar(10), [平均分] decimal(12,2) ) insert @tb select '01','tom','01',89 union select '02','jaky','01',78 union select '03','ekin','02',69 union select '04','andy','02',74select *, [名次]=(select count(1) from @tb where [班级]=A.[班级] and [平均分]>=A.[平均分]) from @tb A where [学号]='01' --注释此行得到所有学生的排名--结果 /*学号 姓名 班级 平均分 名次 ---------- ---------- ---------- -------------- ----------- 01 tom 01 89.00 1(所影响的行数为 1 行) */
select a.*, 名次 = (select count(*)+1 from 表 where 平均分>a.平均分) from 表 aselect a.*, 名次 = isnull(count(b.学号),0)+1 from 表 a left join 表 b where a.平均分<b.平均分 group by a.学号,a.姓名,a.班级编号,a.平均分
create table #t(学号 varchar(4),姓名 varchar(10),班级编号 varchar(4),平均分 int) insert into #t select '01',rtrim('tom '),'01',89 insert into #t select '02',rtrim('jaky'),'01',78 insert into #t select '03',rtrim('ekin'),'01',69 insert into #t select '04',rtrim('andy'),'01',74 insert into #t select '05',rtrim('cimd'),'01',78 insert into #t select '06',rtrim('sdcg'),'02',82 insert into #t select '07',rtrim('clic'),'02',59 insert into #t select '08',rtrim('mary'),'02',62 insert into #t select '09',rtrim('timy'),'02',88 insert into #t select '10',rtrim('judy'),'02',91 select a.*, 名次 = (select count(*)+1 from #t where 班级编号=a.班级编号 and 平均分>a.平均分) from #t a where a.学号 = '01'select a.*, 名次 = isnull(count(b.学号),0)+1 from #t a left join #t b where a.班级编号 = b.班级编号 and a.平均分<b.平均分 and a.a.学号 = '01' group by a.学号,a.姓名,a.班级编号,a.平均分
create table #t (sn varchar(10),nam varchar(20),class varchar(10),results decimal) insert into #t values('01','tom','01',89) insert into #t values('02','jaky','01',78)insert into #t values('03','ekin','02',69) insert into #t values('04','andy','02',74) insert into #t values('05','mabel','01',98) insert into #t values('06','tracy','01',97) insert into #t values('07','tracy','03',97) insert into #t values('08','kitty','01',89)select * from #t/* sn nam class results ---------- -------------------- ---------- -------------------- 01 tom 01 89 02 jaky 01 78 03 ekin 02 69 04 andy 02 74 05 mabel 01 98 06 tracy 01 97 07 tracy 03 97 08 kitty 01 89(8 row(s) affected)*/select t.sn,t.class,t.nam,t.results,pn = (select count(sn) + 1 from #t t1 where t1.sn <> t.sn and t1.class = t.class and t1.results > t.results) from #t t where t.sn = '08' ---相等的分数同样排名 select t.sn,t.class,t.nam,t.results,pn = (select count(sn) + 1 from #t t1 where t1.sn <> t.sn and t1.class = t.class and ( t1.results > t.results or ( t1.results = t.results and t1.sn < t.sn))) from #t t where t.sn = '08' ---相等的分数按号码前后排名/* sn class nam results pn ---------- ---------- -------------------- -------------------- ----------- 08 01 kitty 89 3(1 row(s) affected)sn class nam results pn ---------- ---------- -------------------- -------------------- ----------- 08 01 kitty 89 4(1 row(s) affected) */
select (select count(*) from table1 where id<=a.id) 名次,* from table1 a where bjid in (select bjid from table1 where id='01')
对应你表中的字段就是:select (select count(*) from table1 where 学号<=a.学号) 名次,* from table1 a where 班级编号 in (select 班级编号 from table1 where 学号='01')
在统计学生名次时不可避免需要处理成绩并列的情况,如下例所示: ---------------------------------------------------------------生成测试数据 create table #t(学号 varchar(4),姓名 varchar(10),班级编号 varchar(4),平均分 int) insert into #t select '01',rtrim('tom '),'01',89 insert into #t select '02',rtrim('jaky'),'01',78 insert into #t select '03',rtrim('ekin'),'01',69 insert into #t select '04',rtrim('andy'),'01',74 insert into #t select '05',rtrim('cimd'),'01',78 insert into #t select '06',rtrim('sdcg'),'02',82 insert into #t select '07',rtrim('clic'),'02',59 insert into #t select '08',rtrim('mary'),'02',62 insert into #t select '09',rtrim('timy'),'02',88 insert into #t select '10',rtrim('judy'),'02',91 --执行查询方式一 select a.*, 名次 = (select count(*)+1 from #t where 班级编号=a.班级编号 and 平均分>a.平均分) from #t a order by a.班级编号,名次 --输出结果 学号 姓名 班级编号 平均分 名次 ----- ------- --------- ------ ------ 01 tom 01 89 1 02 jaky 01 78 2 05 cimd 01 78 2 04 andy 01 74 4 03 ekin 01 69 5 10 judy 02 91 1 09 timy 02 88 2 06 sdcg 02 82 3 08 mary 02 62 4 07 clic 02 59 5 --执行查询方式二 select a.*, 名次 = isnull(count(b.学号),0)+1 from #t a left join #t b on a.班级编号 = b.班级编号 and a.平均分<b.平均分 group by a.学号,a.姓名,a.班级编号,a.平均分 order by a.班级编号,名次
number varchar(2),
name varchar(10),
cno varchar(2),
avgscore int
)insert into class values('01','tom','01',89)
insert into class values('02','jacy','01',78)
insert into class values('03','ekin','02',69)
insert into class values('04','andy','02',69)select count(1) from class
where avgscore>=(
select avgscore from class
where number='01' and cno='01'
) and cno='01'
(
[学号] varchar(10),
[姓名] varchar(10),
[班级] varchar(10),
[平均分] decimal(12,2)
)
insert @tb
select '01','tom','01',89 union
select '02','jaky','01',78 union
select '03','ekin','02',69 union
select '04','andy','02',74select *,
[名次]=(select count(1) from @tb where [班级]=A.[班级] and [平均分]>=A.[平均分])
from @tb A
where [学号]='01' --注释此行得到所有学生的排名--结果
/*学号 姓名 班级 平均分 名次
---------- ---------- ---------- -------------- -----------
01 tom 01 89.00 1(所影响的行数为 1 行)
*/
a.*,
名次 = (select count(*)+1 from 表 where 平均分>a.平均分)
from
表 aselect
a.*,
名次 = isnull(count(b.学号),0)+1
from
表 a
left join
表 b
where
a.平均分<b.平均分
group by
a.学号,a.姓名,a.班级编号,a.平均分
insert into #t select '01',rtrim('tom '),'01',89
insert into #t select '02',rtrim('jaky'),'01',78
insert into #t select '03',rtrim('ekin'),'01',69
insert into #t select '04',rtrim('andy'),'01',74
insert into #t select '05',rtrim('cimd'),'01',78
insert into #t select '06',rtrim('sdcg'),'02',82
insert into #t select '07',rtrim('clic'),'02',59
insert into #t select '08',rtrim('mary'),'02',62
insert into #t select '09',rtrim('timy'),'02',88
insert into #t select '10',rtrim('judy'),'02',91
select
a.*,
名次 = (select count(*)+1 from #t where 班级编号=a.班级编号 and 平均分>a.平均分)
from
#t a
where
a.学号 = '01'select
a.*,
名次 = isnull(count(b.学号),0)+1
from
#t a
left join
#t b
where
a.班级编号 = b.班级编号 and a.平均分<b.平均分 and a.a.学号 = '01'
group by
a.学号,a.姓名,a.班级编号,a.平均分
insert into #t values('01','tom','01',89)
insert into #t values('02','jaky','01',78)insert into #t values('03','ekin','02',69)
insert into #t values('04','andy','02',74)
insert into #t values('05','mabel','01',98)
insert into #t values('06','tracy','01',97)
insert into #t values('07','tracy','03',97)
insert into #t values('08','kitty','01',89)select * from #t/*
sn nam class results
---------- -------------------- ---------- --------------------
01 tom 01 89
02 jaky 01 78
03 ekin 02 69
04 andy 02 74
05 mabel 01 98
06 tracy 01 97
07 tracy 03 97
08 kitty 01 89(8 row(s) affected)*/select t.sn,t.class,t.nam,t.results,pn = (select count(sn) + 1 from #t t1 where t1.sn <> t.sn
and t1.class = t.class and t1.results > t.results) from #t t
where t.sn = '08' ---相等的分数同样排名
select t.sn,t.class,t.nam,t.results,pn = (select count(sn) + 1 from #t t1 where t1.sn <> t.sn
and t1.class = t.class and
( t1.results > t.results or ( t1.results = t.results and t1.sn < t.sn))) from #t t
where t.sn = '08' ---相等的分数按号码前后排名/*
sn class nam results pn
---------- ---------- -------------------- -------------------- -----------
08 01 kitty 89 3(1 row(s) affected)sn class nam results pn
---------- ---------- -------------------- -------------------- -----------
08 01 kitty 89 4(1 row(s) affected)
*/
where bjid in (select bjid from table1 where id='01')
where 班级编号 in (select 班级编号 from table1 where 学号='01')
---------------------------------------------------------------生成测试数据
create table #t(学号 varchar(4),姓名 varchar(10),班级编号 varchar(4),平均分 int)
insert into #t select '01',rtrim('tom '),'01',89
insert into #t select '02',rtrim('jaky'),'01',78
insert into #t select '03',rtrim('ekin'),'01',69
insert into #t select '04',rtrim('andy'),'01',74
insert into #t select '05',rtrim('cimd'),'01',78
insert into #t select '06',rtrim('sdcg'),'02',82
insert into #t select '07',rtrim('clic'),'02',59
insert into #t select '08',rtrim('mary'),'02',62
insert into #t select '09',rtrim('timy'),'02',88
insert into #t select '10',rtrim('judy'),'02',91
--执行查询方式一
select
a.*,
名次 = (select count(*)+1 from #t where 班级编号=a.班级编号 and 平均分>a.平均分)
from
#t a
order by
a.班级编号,名次
--输出结果
学号 姓名 班级编号 平均分 名次
----- ------- --------- ------ ------
01 tom 01 89 1
02 jaky 01 78 2
05 cimd 01 78 2
04 andy 01 74 4
03 ekin 01 69 5
10 judy 02 91 1
09 timy 02 88 2
06 sdcg 02 82 3
08 mary 02 62 4
07 clic 02 59 5
--执行查询方式二
select
a.*,
名次 = isnull(count(b.学号),0)+1
from
#t a
left join
#t b
on
a.班级编号 = b.班级编号 and a.平均分<b.平均分
group by
a.学号,a.姓名,a.班级编号,a.平均分
order by
a.班级编号,名次
--输出结果
学号 姓名 班级编号 平均分 名次
----- ------- --------- ------ ------
01 tom 01 89 1
02 jaky 01 78 2
05 cimd 01 78 2
04 andy 01 74 4
03 ekin 01 69 5
10 judy 02 91 1
09 timy 02 88 2
06 sdcg 02 82 3
08 mary 02 62 4
07 clic 02 59 5