结构:
student:
id
name
score:
scid
sid(student:id)
cid(course:id)
score
course:
id
name
数据:
student
id name
1 赵一
2 钱二
3 孙三
4 李四
5 周五
6 吴六
7 郑七
8 王八
9 冯九
10 陈十
11 楚十一
12 魏十二
score
id sid cid score
1 1 1 90.00
2 1 2 140.50
3 1 3 102.50
4 1 4 120.00
5 2 1 90.00
6 2 2 112.00
7 2 3 89.00
8 2 4 73.00
9 3 1 70.00
10 3 2 149.00
11 3 3 66.00
12 3 4 122.00
course
id course
1 语文
2 数学
3 英语
4 化学
问题
1.查询成绩2门以上(含)成绩小于90分学生的姓名
2.查询有一课(含)以上成绩小于90分学生所有课程的平均成绩(就是统计有不及格学生的所有课程平均分，全部及格的不计).
------解决方案--------------------
- SQL code
--> 测试数据：[student]if object_id('[student]') is not null drop table [student]create table [student]([id] int,[name] varchar(6))insert [student]select 1,'赵一' union allselect 2,'钱二' union allselect 3,'孙三' union allselect 4,'李四' union allselect 5,'周五' union allselect 6,'吴六' union allselect 7,'郑七' union allselect 8,'王八' union allselect 9,'冯九' union allselect 10,'陈十' union allselect 11,'楚十一' union allselect 12,'魏十二'--> 测试数据：[score]if object_id('[score]') is not null drop table [score]create table [score]([id] int,[sid] int,[cid] int,[score] numeric(5,2))insert [score]select 1,1,1,90.00 union allselect 2,1,2,140.50 union allselect 3,1,3,102.50 union allselect 4,1,4,120.00 union allselect 5,2,1,90.00 union allselect 6,2,2,112.00 union allselect 7,2,3,89.00 union allselect 8,2,4,73.00 union allselect 9,3,1,70.00 union allselect 10,3,2,149.00 union allselect 11,3,3,66.00 union allselect 12,3,4,122.00--> 测试数据：[course]if object_id('[course]') is not null drop table [course]create table [course]([id] int,[course] varchar(4))insert [course]select 1,'语文' union allselect 2,'数学' union allselect 3,'英语' union allselect 4,'化学'with tas(select a.id,b.name,c.course,a.score,COUNT(1)over(partition by b.name) as totalfrom [score] ainner join [student] b on a.[sid]=b.idinner join [course] con a.cid=c.idwhere a.score<90)--查询成绩2门以上(含)成绩小于90分学生的姓名select distinct name from t where total>=2/*name------钱二孙三*/gowith tas(select a.id,b.name,c.course,a.scorefrom [score] ainner join [student] b on a.[sid]=b.idinner join [course] con a.cid=c.id)select name,AVG(score) as AvgScore from t awhere exists(select 1 from t b where a.name=b.name and b.score<=90)group by name/*name AvgScore钱二 91.000000孙三 101.750000赵一 113.250000*/
------解决方案--------------------
- SQL code
--1SELECT s.* FROM [student] s JOIN(SELECT [sid] FROM [score] WHERE [score]<90GROUP BY [sid] HAVING COUNT(*)>1) a ON a.[sid]=s.[id]--2SELECT c.name , AVG(a.[score])FROM [score] a JOIN(SELECT [sid] FROM [score] WHERE [score]<90GROUP BY [sid] HAVING COUNT(*)>0) b ON a.[sid]=b.[sid]JOIN [student] c ON a.[sid]=c.[id]GROUP BY c.name