10-30 查询平均成绩最高的同学(MSSQL)

本题目要求编写SQL语句，查询平均成绩最高的同学的学号，姓名，性别，年龄及平均成绩。

注意：假设以当前系统时间为2020年来计算学生年龄。

提示：MSSQLServer 评测SQL语句。

表结构:

请在这里写定义表结构的SQL语句。例如：

-- 学生表
CREATE TABLE stu (    
  sno char(4) NOT NULL,
  sname nvarchar(8) NOT NULL,
  sex smallint DEFAULT NULL,
  mno char(2) DEFAULT NULL,
  birdate datetime DEFAULT NULL,
  memo ntext,
  PRIMARY KEY (sno)
);
-- 课程表
CREATE TABLE  cou  (  
   cno  char(4) NOT NULL,
   cname  nvarchar(30) NOT NULL,
   credit  smallint DEFAULT NULL,
   ptime  char(5) DEFAULT NULL,
   teacher  nvarchar(10) DEFAULT NULL,
  PRIMARY KEY ( cno )
);
-- 学生选课成绩表
CREATE TABLE  sc  (  
   sno  char(4) NOT NULL,  -- 学生学号
   cno  char(4) NOT NULL,  -- 课程号
   grade  decimal(4,1) DEFAULT NULL,
  PRIMARY KEY ( sno , cno ),
  CONSTRAINT  fk_sc_cno  FOREIGN KEY ( cno ) REFERENCES  cou  ( cno ),
  CONSTRAINT  fk_sc_sno  FOREIGN KEY ( sno ) REFERENCES  stu  ( sno )
)
 

 

表样例

这里给出上述表结构对应的表样例。例如

``stu```表：

 sc表：

 

输出样例:

请在这里给出输出样例。例如：

 思路：

1. 首先，我们使用一个子查询（子查询a）来获取学生的学号、姓名、性别、年龄和平均成绩。在子查询中，我们使用了JOIN操作将学生表（stu）和学生选课表（sc）连接起来。通过stu.sno和sc.sno的关联，我们可以获取到学生的学号、姓名、性别和出生日期。在子查询中，我们使用了YEAR函数计算学生的年龄，使用AVG函数计算学生的平均成绩。最后，使用GROUP BY子句将结果按学生编号、姓名、性别和出生日期进行分组。

2. 接下来，在主查询中，我们使用WHERE子句来筛选出平均成绩等于子查询a中最高平均成绩的学生。为此，我们使用了另一个子查询（子查询b）来获取子查询a中的最高平均成绩。在子查询b中，我们重复了子查询a的逻辑，并使用MAX函数获取最高平均成绩。

3. 最后，我们在主查询中选择子查询a中的学号、姓名、性别、年龄和平均成绩作为结果，并将其命名为a.学号、a.姓名、a.性别、a.年龄和a.平均成绩。

select a.学号,a.姓名,a.性别,a.年龄,a.平均成绩 from
(select stu.sno 学号,stu.sname 姓名,stu.sex 性别
,2020-year(stu.birdate) 年龄,avg(sc.grade) 平均成绩
from stu join sc on sc.sno=stu.sno
group by stu.sno,stu.sname,stu.sex,stu.birdate
) a
where a.平均成绩=( select max(平均成绩) from
(select stu.sno 学号,stu.sname 姓名,stu.sex 性别
,2020-year(stu.birdate) 年龄,avg(sc.grade) 平均成绩
from stu join sc on sc.sno=stu.sno
group by stu.sno,stu.sname,stu.sex,stu.birdate
) a
)