ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据库:SQL Server SQL语句练习

数据库:SQL Server SQL语句练习

原创 Linux操作系统 作者:果果的空间 时间:2013-08-14 10:50:47 0 删除 编辑
/*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#
/*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 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  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博客 ” ,链接:http://blog.itpub.net/29056818/viewspace-768459/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-18

  • 博文量
    60
  • 访问量
    117226