SQL多表查询解决方法

SQL多表查询
结构:
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
11190.00
212140.50
313102.50
414120.00
52190.00
622112.00
72389.00
82473.00
93170.00
1032149.00
113366.00
1234122.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