sql语言多表查询

1查询有不及格成绩的学生姓名

select studName from T_stud

join T_select on T_studstudNo = T_selectstudNo

where score < 60

2查询有选课的学生姓名和选课数量

select studName,count() from T_stud

right join (select studNo,count()

from T_select where 1=1 having count() > 0

group by studNo ) as A

on T_studstudNo = AstudNo

3查询选修了60岁以上老师所教课程的所有学生

select distinct BstudName

from ( select AstudNo,AstudName,teacherNo

from ( select studNo,studName,subNo

from T_stud join T_select

on T_studstudNo = T_selectstudNo ) as A

join T_Sub on T_SubsubNo = AsubNo ) as B

join T_teacher on T_teacherteacherNo = BteacherNo

where T_teacherteacherAge > 60

4洪七公老师的学生中，考试不及格的人数（姓名列表）

select distinct BstudName

from ( select AstudNo,AstudName,teacherNo

from ( select studNo,studName,subNo

from T_stud join T_select

on T_studstudNo = T_selectstudNo

where T_selectscore < 60 ) as A

join T_Sub on T_SubsubNo = AsubNo ) as B

join T_teacher on T_teacherteacherNo = BteacherNo

where T_teacherteacherName like '洪七公'

5洪七公老师教的学生列表以及选修的课程

select BstudName,BsubName

from ( select AstudNo,AstudName,teacherNo,subName

from ( select studNo,studName,subNo

from T_stud join T_select

on T_studstudNo = T_selectstudNo

where T_selectscore < 60 ) as A

join T_Sub on T_SubsubNo = AsubNo ) as B

join T_teacher on T_teacherteacherNo = BteacherNo

where T_teacherteacherName like '洪七公'

6所有大于50岁老师姓名列表

select teacherName from T_teacher

where teacherAge > 50

7郭靖的课程平均分

select avg(score) from T_stud

join T_select on T_studstudNo = T_selectstudNo

where studName like '郭靖'

8王语嫣所选课程名称列表

select subName

from （select subNo

from T_stud join T_select

on T_studstudNo = T_selectstudNo

where studName like '王语嫣') as A

join T_Sub on T_SubsubNo = AsubNo

9选修学生最多的课程(这里没有考虑同时多门课程的选修数相等的情况)

select subName

from (select top(1) subNo,count()

from T_select

group by subNo order by subNo desc ) as A

join T_Sub on T_SubsubNo = AsubNo

10所教学生最多的老师

其他的没时间了，你自己去试着写写吧。。。

SELECT RI姓名, RI职务 FROM RI JOIN BI JOIN RB ON RI读者 = RB读者 ON BI图书编号 = RB图书编号 WHERE RB图书编号='TFTS003'

SELECT RI姓名, BI图书名, RB借书日期 FROM RI JOIN BI JOIN RB ON RI读者 = RB读者 ON BI图书编号 = RB图书编号 WHERE RI姓名='陈红'

SELECT FROM RI WHERE RI读者 IN (SELECT RB读者 FROM RB GROUP BY RB读者 HAVING COUNT() = 2 )

SELECT RI姓名, RI职务 FROM RI JOIN BI JOIN RB ON RI读者 = RB读者 ON BI图书编号 = RB图书编号 WHERE RB图书名='VB高级编程'

SELECT RI姓名, RI职务 FROM RI JOIN BI JOIN RB ON RI读者 = RB读者 ON BI图书编号 = RB图书编号 WHERE DATEDIFF( day, RB借书日期, ( SELECT RB借书日期 FROM RI JOIN RB ON RI读者 = RB读者 WHERE RI姓名 = '张三') ) = 0

select scoretable,table1 from scoretable,table1 where scoretablenum=table1num and table1A>11

首先要检查你的表与表之间是不是有约束（主外键约束），如果存在，才可以像 上面这位朋友的方式进行连接，一般连接有左连接、右连接、内连接，下面给你举例：\x0d\\x0d\----做笛卡尔积\x0d\select sid,sname,scid,scsname,scscore from infom s ,score sc\x0d\\x0d\------内连接 写法一\x0d\select sid,sname,scid,scsname,scscore \x0d\from infom s ,score sc inner join score sc\x0d\on sid= scid ------内连接的条件\x0d\------on sid scid --------是全集 - 交集\x0d\------where scscore>80 \x0d\\x0d\------内连接 方法二\x0d\select sid,sname,scid,scsname,scscore \x0d\from infom s ,score sc\x0d\where sid= scid \x0d\\x0d\------\x0d\\x0d\-------------------------------------------------------外连接 左连接\x0d\--------------左表数据完全显示，右表中相同的数据显示，不同数据null\x0d\select Studentname,scorescore\x0d\from Student left join score -----------------先写的为左表\x0d\on Studentid=score id -----------------连接条件\x0d\\x0d\-------------------------------------------------------外连接 右连接\x0d\--------------右表数据完全显示，左表中相同的数据显示，不同数据显示null\x0d\select Studentname,scorescore \x0d\from Student right join score \x0d\on Studentid=score id \x0d\\x0d\-------------------------------------------------------全连接 full join\x0d\-------------------------------------------------------左、右表的数据完全显示，相同的数据显示一次\x0d\select Studentname,scorescore \x0d\from Student full join score \x0d\on Studentid=score id \x0d\\x0d\-------------------------------------------------------交叉联接\x0d\------------------------------------------交叉联接得到的是两表联接所有的数据组合\x0d\------------------------------------------（A表的数据记录 B 表的数据记录）\x0d\-------------------------------------------方式一\x0d\select Student,score from Student,score \x0d\-------------------------------------------方式二\x0d\select score ,Student from Student \x0d\cross join score \x0d\\x0d\-----------------------------------------------------多表联接\x0d\--------------------------------------要求查出张三 C#的考试成绩，涉及student，score，subject三个表\x0d\---------方式一:\x0d\select studentname,subjectsname ,score score\x0d\from Student\x0d\inner join score\x0d\on studentid= scoreid\x0d\inner join subject \x0d\on scoreid=subjectid\x0d\where Studentname='张三and subjectsname='C#'\x0d\\x0d\---------方式二:等值联接\x0d\select studentname,subjectsname ,score score\x0d\from Student,score ,subject\x0d\where StudentDBid=scoreid and score id=subjectid\x0d\ and Studentname='张三and subjectsname='C#'

以上就是关于sql语言多表查询全部的内容，包括:sql语言多表查询、SQL 多表查询题目 急！在线等！、java数据库 *** 作如何进行多表查询等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！