数据库中有两张表:stu_score与stu_info
表stu_score中的字段如下:
Lesion_id | score | stuid |
1 | 89 | 1001 |
2 | 80 | 1001 |
3 | 70 | 1001 |
1 | 90 | 1002 |
2 | 70 | 1002 |
3 | 80 | 1002 |
表stu_info中字段如下:
学号:stu_id 姓名:name 性别:gender 班级号:class_id
stu_id | name | gender | class_id |
1001 | 张三 | 男 | 232 |
1002 | 李四 | 女 | 233 |
1003 | 王五 | 女 | 233 |
1004 | 赵六 | 男 | 235 |
1005 | 懂八 | 男 | 231 |
1006 | 齐十 | 女 | 236 |
1. 查出张三各科目平均成绩。
select sc.Lesion_id,avg(sc.score) from stu_info as s,stu_score as sc where sc.stuid=s.stu_id AND s.name='张三' GROUP BY sc.Lesion_id;
2. 查出各个班级所有人的平均成绩
select s.class_id,avg(sc.score) from stu_info as s,stu_score as sc where sc.stuid=s.stu_id GROUP BY s.class_id;
3. 查出各科成绩最高的学生的名称、学号、课程号、分数。
select s.stu_id,s.name,sc.Lesion_id,sc.score from stu_info s,stu_score sc where sc.stuid=s.stu_id and sc.score in (select max(sc.score) from stu_score sc GROUP BY sc.Lesion_id);