用SQL查询平均成绩高于90分的同学的学号、姓名及其平均成绩,并按成绩由高到低排序

Question

如题所述

Answer 1

select 学号,姓名,avg(成绩) as '平均成绩' from 学生成绩表 HAVING avg(成绩) > 90 order by avg(成绩) desc

Answer 2

create database StudentsDB
go
use StudentsDB
go
if exists(select name from sysobjects where name='Student' and xtype='u')
drop table Student
go
create table Student
(
StuID int,
name varchar(10),
)
go
insert into Student(StuID,name) values(1,'aa')
insert into Student(StuID,name) values(2,'bb')
insert into Student(StuID,name) values(3,'cc')
insert into Student(StuID,name) values(4,'dd')
go
create table CJ
(
StuID int,
km varchar(10),
kmcj float
)
go
insert into CJ(StuID,km,kmcj) values(1,'yw',78.5)
insert into CJ(StuID,km,kmcj) values(1,'sx',86)
insert into CJ(StuID,km,kmcj) values(1,'yy',69.7)
insert into CJ(StuID,km,kmcj) values(2,'yw',91)
insert into CJ(StuID,km,kmcj) values(2,'sx',79)
insert into CJ(StuID,km,kmcj) values(2,'yy',66)
insert into CJ(StuID,km,kmcj) values(3,'yw',81)
insert into CJ(StuID,km,kmcj) values(3,'sx',90)
go
select s.StuID,s.name,AVG(c.kmcj) as avgs from Student s join CJ c
on s.StuID = c.StuID group by s.StuID,s.name having AVG(c.kmcj)>80 order by avgs

Answer 3

select 学号,姓名,avg(成绩) as '平均成绩' from 学生成绩表 where avg(成绩) > 90 order by avg(成绩) desc这样试试...