SQL多表查询

Question

结构:
student:
id
namescore:
scid
sid(student:id)
cid(course:id)
scorecourse:
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.00course
id course
1 语文
2 数学
3 英语
4 化学问题
1.查询成绩2门以上(含)成绩小于90分学生的姓名
2.查询有一课(含)以上成绩小于90分学生所有课程的平均成绩(就是统计有不及格学生的所有课程平均分，全部及格的不计).

Answer 1

--> 测试数据：[student]
if object_id('[student]') is not null drop table [student]
create table [student]([id] int,[name] varchar(6))
insert [student]
select 1,'赵一' union all
select 2,'钱二' union all
select 3,'孙三' union all
select 4,'李四' union all
select 5,'周五' union all
select 6,'吴六' union all
select 7,'郑七' union all
select 8,'王八' union all
select 9,'冯九' union all
select 10,'陈十' union all
select 11,'楚十一' union all
select 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 all
select 2,1,2,140.50 union all
select 3,1,3,102.50 union all
select 4,1,4,120.00 union all
select 5,2,1,90.00 union all
select 6,2,2,112.00 union all
select 7,2,3,89.00 union all
select 8,2,4,73.00 union all
select 9,3,1,70.00 union all
select 10,3,2,149.00 union all
select 11,3,3,66.00 union all
select 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 all
select 2,'数学' union all
select 3,'英语' union all
select 4,'化学'
with t
as(
select a.id,b.name,c.course,a.score,
COUNT(1)over(partition by b.name) as total
from [score] a
inner join [student] b on 
a.[sid]=b.id
inner join [course] c
on a.cid=c.id
where a.score<90
)
--查询成绩2门以上(含)成绩小于90分学生的姓名
select distinct name from t where total>=2
/*
name
------
钱二
孙三
*/
go
with t
as(
select a.id,b.name,c.course,a.score
from [score] a
inner join [student] b on 
a.[sid]=b.id
inner join [course] c
on a.cid=c.id
)
select name,AVG(score) as AvgScore 
from t a
where 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
*/

Answer 2

--1
SELECT s.* FROM [student] s JOIN
(
SELECT [sid] FROM [score] WHERE  [score]<90
GROUP BY [sid] 
HAVING COUNT(*)>1
) a ON a.[sid]=s.[id]
--2
SELECT c.name , AVG(a.[score])FROM [score] a JOIN
(
SELECT [sid] FROM [score] WHERE  [score]<90
GROUP BY [sid] 
HAVING COUNT(*)>0
) b ON a.[sid]=b.[sid]
JOIN [student] c ON a.[sid]=c.[id]
GROUP BY c.name