sql语言多表查询

sql语言多表查询

1.查询有不及格成绩的学生姓名 select studName from T_stud join T_select on T_stud.studNo = T_select.studNo 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_stud.studNo = A.studNo 3.查询选修了60岁以上老师所教课程的所有学生 select distinct B.studName from ( select A.studNo,A.studName,teacherNo from ( select studNo,studName,subNo from T_stud join T_select on T_stud.studNo = T_select.studNo ) as A join T_Sub on T_Sub.subNo = A.subNo ) as B join T_teacher on T_teacher.teacherNo = B.teacherNo where T_teacher.teacherAge > 60 4.洪七公老师的学生中，考试不及格的人数（姓名列表） select distinct B.studName from ( select A.studNo,A.studName,teacherNo from ( select studNo,studName,subNo from T_stud join T_select on T_stud.studNo = T_select.studNo where T_select.score < 60 ) as A join T_Sub on T_Sub.subNo = A.subNo ) as B join T_teacher on T_teacher.teacherNo = B.teacherNo where T_teacher.teacherName like '洪七公' 5.洪七公老师教的学生列表以及选修的课程 select B.studName,B.subName from ( select A.studNo,A.studName,teacherNo,subName from ( select studNo,studName,subNo from T_stud join T_select on T_stud.studNo = T_select.studNo where T_select.score < 60 ) as A join T_Sub on T_Sub.subNo = A.subNo ) as B join T_teacher on T_teacher.teacherNo = B.teacherNo where T_teacher.teacherName like '洪七公' 6.所有大于50岁老师姓名列表 select teacherName from T_teacher where teacherAge > 50 7.郭靖的课程平均分 select avg(score) from T_stud join T_select on T_stud.studNo = T_select.studNo where studName like '郭靖' 8.王语嫣所选课程名称列表 select subName from （select subNo from T_stud join T_select on T_stud.studNo = T_select.studNo where studName like '王语嫣') as A join T_Sub on T_Sub.subNo = A.subNo 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_Sub.subNo = A.subNo 10.所教学生最多的老师 其他的没时间了，你自己去试着写写吧。。。

T-SQL里的查询语句,完整的是怎样的?

SELECT select_list

[ INTO new_table ]

FROM table_source

[ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

可以在查询之间使用 UNION 运算符，以将查询的结果组合成单个结果集。