求一个查询语句，今天的一道面试题！
表T1 字段StudentID，StudentName;
表T2 字段StudentID，CourseID，Score;
表T3 字段CourseID ， CourseName
查询出每门课成绩都大于课程平均分的同学的名字和课程名
[解决办法]
select studentid from T2 A,
(select CourseID,avg(Score) Score from T2 group by CourseID) B
where A.CourseID = B.CourseID and A.Score > B.Score
group by studentid having count(*) = (select count(*) from T3)
[解决办法]
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 sishuxue
li siyuwen
[解决办法]
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
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))
[解决办法]
如果把课程名也要显示出来 Try
Select A.StudentName,C.CourseNameFrom 表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 And Exists (Select StudentID From 表T2 Where StudentID=A.StudentID Group By StudentID Having Count(1)=(Select Count(1) From 表T3))
[解决办法]
哈哈这个是交通银行信用卡中心的面试题吧
[解决办法]
select *from t2 p1 where not exists (select t2.StudentID from
t2 p2 where score<avg(score) and p1.StudentID=p2.StudentID )
给我分数吧我这个最简单
