--> 测试数据：[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
*/

------解决方案--------------------

SQL code
--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