查询语句，今天的一道面试题

select T1.StudentName, T3.CourseNamefrom T1JOIN T2 ON T1.StudentID = T2.StudentIDJOIN T3 ON T2.CourseID = T3.CourseIDwhere T1.StudentID in(    select StudentID     from T1    Where NOT Exists (Select score from T2 tt where StudentID = T1.StudentID And score <= (Select Avg(score)from T2 where courseID = tt.courseID)))
------解决方案--------------------
select T.StudentName , T3.CourseName from
(
 select C.StudentName from T2 A, T1 C,
 (select CourseID,avg(Score) Score from T2 group by CourseID) B
 where A.CourseID = B.CourseID and A.Score > B.Score and C.StudentID = A.StudentID
 group by StudentName having count(*) = (select count(*) from T3)
) T
CROSS JOIN T3
------解决方案--------------------
select t1.StudentName,t3.CourseName from t2 left join t1 on t2.StudentID=t1.StudentID
left join t3 on t2.CourseID=t3.CourseID where t2.studentid  
in  
(
select StudentID
from
(select courseid,avg(score) as score from t2 group by courseid) t,
t2  
where  
t2.CourseID=t.CourseID  
and t2.score>t.score  
group by StudentID
having count(t2.CourseID)=(select count(*) from t3)
)

------解决方案--------------------
/*测试环境*/

create table t1  
(
StudentID int,
StudentName varchar(100)
)
create table t2
(
StudentID int,
CourseID int,
Score int
)
create table t3
(
CourseID int,
CourseName varchar(100)  
)

insert t1 select 1,'zhang san'
union all  
select 2,'li si'
union all
select 3,'wang wu'

truncate table t2
insert t2 select 1,1,20
union all
select 1,2,40
union all  
select 2,1,50
union all  
select 2,2,80
union all
select 3,1,30
union all
select 3,2,80

insert t3 select 1,'shuxue'
union all
select 2,'yuwen'


/*sql语句*/
select t1.StudentName,t3.CourseName  
from t2  
left join t1 on t2.StudentID=t1.StudentID
left join t3 on t2.CourseID=t3.CourseID  
where t2.studentid  
in  
(
select StudentID
from
(select courseid,avg(score) as score from t2 group by courseid) t,
t2  
where  
t2.CourseID=t.CourseID  
and t2.score>t.score  
group by StudentID
having count(t2.CourseID)=(select count(*) from t3)
)


/*结果*/
li si	shuxue
li si	yuwen

------解决方案--------------------
SQL code
Select A.StudentName,C.CourseName From 表T1 As A,表T2 As B,表T3 As C,(Select CourseID,Avg(Score) As Score From 表T2 Group By CourseID) As DWhere A.StudentID=B.StudentID And B.CourseID=C.CourseID And B.CourseID=D.CourseID And B.Score>D.Score
------解决方案--------------------
理解错了????
LZ,只显示姓名就可以了吧,为什么把课程也要显示出来!Try
SQL code
Select       Distinct A.StudentNameFrom       表T1 As A,      表T2 As B,     (Select CourseID,Avg(Score) As Score From 表T2 Group By CourseID) As C,     (Select StudentID,Count(1) As Counts From 表T2 Group By StudentID) As DWhere A.StudentID=B.StudentID And B.CourseID=C.CourseID And B.Score>C.Score And A.StudentID=D.StudentID      And D.Counts=(Select Count(1) From 表T3) ---ORSelect       Distinct A.StudentNameFrom             表T1 As A,      表T2 As B,     (Select CourseID,Avg(Score) As Score From 表T2 Group By CourseID) As CWhere A.StudentID=B.StudentID And B.CourseID=C.CourseID And B.Score>C.Score And Exists      (Select StudentID       From 表T2       Where StudentID=A.StudentID       Group By StudentID Having Count(1)=(Select Count(1) From 表T3))