询问记录平均值及并列排在一条线序

 联系我们     |      2020-01-11 00:50

select t1.* , px = (select count(distinct 平均成绩State of Qatar from ( select m.S# [学员编号] , m.Sname [学员姓名] , isnull(cast(avg(score) as decimal(18,2)),0) [平均战绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname卡塔尔国 t2 where 平均成绩 = t1.平分战表卡塔尔国 from ( select m.S# [学生编号] , m.Sname [学子姓名] , isnull(cast(avg(score) as decimal(18,2)),0) [平均战表] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname) t1order by px

/*数据表结构

--1.学生表Student(S#,Sname,Sage,Ssex) --S# 学子编号,Sname 学子姓名,Sage 出生年月,Ssex 学子性别--2.课程表 Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号--3.教育工我表 Teacher(T#,Tname) --T# 助教编号,Tname 助教姓名--4.成就表 SC(S#,C#,score) --S# 学子编号,C# 课程编号,score 分数

sql server 查询记录平均值及并列排在一条线序 的言辞查询学生的平分成绩并实行排名,sql 二零零二用子查询完结,分平均成绩再一次时保留排名空缺和不保留排名空缺二种。select t1.* , px = (select count(1) from ( select m.S# [学员编号] , m.Sname [学员姓名] , isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname卡塔尔(قطر‎t2 where 平均成绩 t1.等分成绩卡塔尔(قطر‎ + 1 from ( select m.S# [学子编号] , m.Sname [学子姓名] , isnull(cast(avg(score) as decimal(18,2)),0) [平均成绩] from Student m left join SC n on m.S# = n.S# group by m.S# , m.Sname) t1order by px