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.CourseName from T1 JOIN T2 ON T1.StudentID = T2.StudentID JOIN T3 ON T2.CourseID = T3.CourseID where 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
请问为什么ls为什么需要 group by studentid having count(*) = (select count(*) from 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,80insert 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
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 D Where A.StudentID=B.StudentID And B.CourseID=C.CourseID And B.CourseID=D.CourseID And B.Score>D.Score
理解错了???? LZ,只显示姓名就可以了吧,为什么把课程也要显示出来!TrySelect Distinct A.StudentName From 表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 D Where 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) ---OR Select Distinct A.StudentName From 表T1 As A, 表T2 As B, (Select CourseID,Avg(Score) As Score From 表T2 Group By CourseID) As C Where 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))
如果把课程名也要显示出来 TrySelect 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 D Where 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 ) 给我分数吧我这个最简单
写写大家,刚才我自己终于想出来了 create database t use t create table t1 ( StudentID int primary key identity, StudentName varchar(20) )create table t2 ( StudentID int foreign key references t1(StudentID), CourseID int foreign key references t3(CourseID), Score int )create table t3 ( CourseID int primary key identity, CourseName varchar(20) )insert into t1 values('jack') insert into t1 values('') insert into t1 values('nick') insert into t1 values('link') insert into t1 values('genk') insert into t3 values('数学') insert into t3 values('英语') insert into t3 values('物理')insert into t2 values(1,1,60) insert into t2 values(1,2,70) insert into t2 values(1,3,80)insert into t2 values(2,1,50) insert into t2 values(2,2,60) insert into t2 values(2,3,70)insert into t2 values(3,1,70) insert into t2 values(3,2,80) insert into t2 values(3,3,90)insert into t2 values(4,1,10) insert into t2 values(4,2,20) insert into t2 values(4,3,30)insert into t2 values(5,1,100) insert into t2 values(5,2,100) insert into t2 values(5,3,100)select studentName,coursename from t1 join t2 on t1.studentid = t2.studentid join t3 on t2.courseid = t3.courseid where t1.studentid in (select t2.studentID--,t2.courseID,t2.Score,A.avgScore from t2 join (select courseID,avg(score) as avgScore from t2 Group By courseID) as A on t2.courseID = A.courseID where t2.Score > A.avgScore group by t2.studentID)
想出来了,还是要给分的嘛 ^ ^ hohoho
不好意思写错一点 下面正确 很短的 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 ' select *from #t2 p1 where not exists (select * from #t2 p2 where score <(select avg(score)from #t2) and p1.StudentID=p2.StudentID ) 测试结果2 1 50 2 2 80
select t1.StudentName,t3.CourseName,t2.Score from t1 left join t2 on t1.studentid=t2.studentid left join t3 on t2.courseID=t3.courseID left join (select CourseID,avg(Score) Score from T2 group by CourseID) Bon B.CourseID=t2.CourseID where t2.Score>B.Score
(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.CourseName
from T1
JOIN T2 ON T1.StudentID = T2.StudentID
JOIN T3 ON T2.CourseID = T3.CourseID
where 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 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
group by studentid having count(*) = (select count(*) from T3)
谢谢
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)
)
(
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,80insert 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
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 D
Where A.StudentID=B.StudentID And B.CourseID=C.CourseID And B.CourseID=D.CourseID And B.Score>D.Score
LZ,只显示姓名就可以了吧,为什么把课程也要显示出来!TrySelect
Distinct A.StudentName
From
表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 D
Where 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) ---OR
Select
Distinct A.StudentName
From
表T1 As A,
表T2 As B,
(Select CourseID,Avg(Score) As Score From 表T2 Group By CourseID) As C
Where 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))
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 D
Where 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 )
给我分数吧我这个最简单
create database t
use t
create table t1
(
StudentID int primary key identity,
StudentName varchar(20)
)create table t2
(
StudentID int foreign key references t1(StudentID),
CourseID int foreign key references t3(CourseID),
Score int
)create table t3
(
CourseID int primary key identity,
CourseName varchar(20)
)insert into t1
values('jack')
insert into t1
values('')
insert into t1
values('nick')
insert into t1
values('link')
insert into t1
values('genk')
insert into t3
values('数学')
insert into t3
values('英语')
insert into t3
values('物理')insert into t2
values(1,1,60)
insert into t2
values(1,2,70)
insert into t2
values(1,3,80)insert into t2
values(2,1,50)
insert into t2
values(2,2,60)
insert into t2
values(2,3,70)insert into t2
values(3,1,70)
insert into t2
values(3,2,80)
insert into t2
values(3,3,90)insert into t2
values(4,1,10)
insert into t2
values(4,2,20)
insert into t2
values(4,3,30)insert into t2
values(5,1,100)
insert into t2
values(5,2,100)
insert into t2
values(5,3,100)select studentName,coursename
from t1 join t2
on t1.studentid = t2.studentid join t3
on t2.courseid = t3.courseid
where t1.studentid in (select t2.studentID--,t2.courseID,t2.Score,A.avgScore
from t2 join (select courseID,avg(score) as avgScore
from t2
Group By courseID) as A
on t2.courseID = A.courseID
where t2.Score > A.avgScore
group by t2.studentID)
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 '
select *from #t2 p1 where not exists (select * from
#t2 p2 where score <(select avg(score)from #t2)
and p1.StudentID=p2.StudentID )
测试结果2 1 50
2 2 80
left join t2 on t1.studentid=t2.studentid
left join t3 on t2.courseID=t3.courseID
left join
(select CourseID,avg(Score) Score from T2 group by CourseID) Bon B.CourseID=t2.CourseID
where t2.Score>B.Score