/*42、查询不同课程成绩相同的学生的学号、课程号、学生成绩*/
select t1.C#, t1.S#, t1.score from
SC t1 join SC t2
on t1.C#!=t2.C# and t1.score=t2.score and t1.S# = t2.S#
SC t1 join SC t2
on t1.C#!=t2.C# and t1.score=t2.score and t1.S# = t2.S#
/*42、查询不同课程成绩相同的学生的学号、课程号、学生成绩*/
select SC.C# from SC where S# in (
select st.S#,(select max(score) from SC where S#=st.S#) as score
from (
select s# from SC group by s#) as st
where (select max(score) from SC where S#=st.S#)=(select min(score) from SC where S#=st.S#)) as scr
inner join SC on scr.S# = SC.S# AND scr.score = SC.score
/*42、查询不同课程成绩相同的学生的学号、学生成绩*/
select st.S#,(select max(score) from SC where S#=st.S#) as score
from (
select s# from SC group by s#) as st
where (select max(score) from SC where S#=st.S#)=(select min(score) from SC where S#=st.S#)
select st.S#,(select max(score) from SC where S#=st.S#) as score
from (
select s# from SC group by s#) as st
where (select max(score) from SC where S#=st.S#)=(select min(score) from SC where S#=st.S#)
select s.score from SC s group by s.score
select s# from SC group by s#
/*42、查询不同课程成绩相同的学生的学号、课程号、学生成绩（学号重复了）*/
select scr.*,sc.C# from (
select st.S#,(select max(score) from SC where S#=st.S#) as score
from (
select s# from SC group by s#) as st
where (select max(score) from SC where S#=st.S#)=(select min(score) from SC where S#=st.S#)) as scr
inner join SC on scr.S# = SC.S# AND scr.score = SC.score
select scr.*,sc.C# from (
select st.S#,(select max(score) from SC where S#=st.S#) as score
from (
select s# from SC group by s#) as st
where (select max(score) from SC where S#=st.S#)=(select min(score) from SC where S#=st.S#)) as scr
inner join SC on scr.S# = SC.S# AND scr.score = SC.score
select scr.*,sc.C# from (
select st.S#,(select max(score) from SC where S#=st.S#) as score
from (
select s# from SC group by s#) as st
where (select max(score) from SC where S#=st.S#)=(select min(score) from SC where S#=st.S#)) as scr
inner join SC on scr.S# = SC.S# AND scr.score = SC.score
select st.S#,(select max(score) from SC where S#=st.S#) as score
from (
select s# from SC group by s#) as st
where (select max(score) from SC where S#=st.S#)=(select min(score) from SC where S#=st.S#)) as scr
inner join SC on scr.S# = SC.S# AND scr.score = SC.score
select s.score from SC s group by s.score
select stu.S#,stu.Sname,s.C#,s.score from Student stu inner join SC s on stu.S#=s.S# and stu.S#=4
select C#,Cname from Course group by C#,Cname
select c1.C#,c2.Cname from Course c1
left join Course c2 on c1.C#=c2.C# group by c1.C#,c2.C#,c1.Cname,c2.Cname
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29056818/viewspace-768459/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
