第八单元 嵌套查询

select * from 班级表 where 班级Id=
(
    Select 班级Id from 学生表 where 姓名 = '叶星辰'
)

select 列名 from 表名 where 字段名 运算符   -- 外层主查询，也叫父查询
(
    select 列名 from 表名 where 条件      -- 内层查询，也叫子查询
)

--4.--查询软件技术1班的所有学生信息
-- 1.找表：学生表，班级表，外键：ClassId
-- 2.根据已经条件查询外键的值
-- 3.根据外键的值查询出题目的要求结果
select * from StudentInfo where ClassId =
(
    select Id from ClassInfo where Name='软件技术1班'
)
​
    
--5.--查询任我行同学的所有成绩
-- 5.1  StudentInfo,StudentScore,找外键  stuId
-- 5.2  先写已知条件 ，将外键查询出来
-- 5.3  根据外键的值查询题目要求的成绩信息
select * from StudentScore where stuId in
(
    select stuId  from StudentInfo where stuName='任我行'
)
​
​
--6.--查询“张三”同学所在班级信息
-- 1  StudentInfo,ClassInfo,找外键  classId
-- 2  先写已知条件 ，将外键查询出来
-- 3  根据外键的值查询题目要求的班级信息
select * from ClassInfo where Id in
(
    select  classId from StudentInfo where stuName='张三' 
)
​
--7.-- 查询学号为“180325011”的同学所在班级所有男生的信息
-- 1  StudentInfo,StudentInfo,关联字段  classId
-- 2  先写已知条件 ，将关联字段查询出来
-- 3  根据关联字段的值查询题目要求的男生的信息
select * from StudentInfo where ClassId=
(
    select ClassId from StudentInfo where stuId='180325011'
) and stuSex='男'
​
​
​
--8.-- 查询班级名为“软件技术1班”一共有多少个女生信息
-- 1  ClassInfo,StudentInfo,关联字段  classId
-- 2  先写已知条件 ，将关联字段查询出来
-- 3  根据关联字段的值查询题目要求的女生信息
select * from StudentInfo where stuSex='女' and ClassId=
(
    select Id from ClassInfo where Name='软件技术1班'
)
​
​
--9.-- 查询电话号为“18899251152”同学所在的班级信息
-- 1  ClassInfo,StudentInfo,关联字段  classId
-- 2  先写已知条件 ，将关联字段查询出来
-- 3  根据关联字段的值查询题目要求的女生信息
select * from ClassInfo where Id=
(
    select classId from StudentInfo where stuPhone='18899251152'
)
​
​
--10.-- 查询所有成绩高于平均分的学生信息
-- 1,StudentScore, StudentInfo, 关联字段：StuId
-- 已知条件是:平均分
select * from StudentInfo where stuId in
(
    -- 查询出高于平均分的Stuid
    select stuId from StudentScore where skillScore>
    (
        select avg(skillScore)  from StudentScore
    )   
)
​
​
​
--11.查询所有年龄小于平均年龄的学生信息
​
-- 计算小于平均年龄的学生信息
select * from StudentInfo where (year(getdate())-year(stuBirthday))<
(
    -- 计算平均年龄
    select avg(year(getdate())-year(stuBirthday)) from StudentInfo
)
​
​
--12.查询不是软件技术1班级的学生信息
-- 关联字段：ClassId
select * from StudentInfo where ClassId not in -- 用in一定不会错，如果子查询的结果只有一条记录时才可以写=
(
    select Id from ClassInfo where Name='软件技术1班'
)
​
select * from StudentInfo where ClassId !=
(
    select Id from ClassInfo where Name='软件技术1班'
)
​
​
--13.查询所有班级人数高于平均人数的班级信息
-- 每个班有多少人
select * from ClassInfo where Id in
(
    select  ClassId from StudentInfo group by ClassId having count(stuId)>
    (
        -- 求平均人数
        select avg(人数)from
        (
            select  count(stuId) as 人数 from StudentInfo group by ClassId
        ) aa
    )
)
​
​
--14.查询成绩最高的学生信息
select * from StudentInfo where stuId in
(
    select stuId from StudentScore where skillScore =
    (
        select MAX(skillScore)  from StudentScore
    )   
)
​
​
​
​
--16.查询班级名是“会计1班”所有学生(使用in 关键字查询)
select * from StudentInfo where ClassId in
(
    select Id from ClassInfo where Name='会计1班'
)
​
​
--17.查询年龄是16、18、21岁的学生信息
select * from StudentInfo where (year(getdate())-year(stuBirthday)) in (16,18,21)
​
​
--18.查询所有17-20岁且成绩高于平均分的女生信息
select * from StudentInfo where (year(getdate())-year(stuBirthday)) between 17 and 20
and stuSex='女' and stuId in
(
select stuId from StudentScore where skillScore>
(
select avg(skillScore) from StudentScore
)
)
​
​
--19.查询不包括'张三'、'王明'、'肖义'的所有学生信息(not in 关键字查询)
select * from StudentInfo where stuName not in('张三','王明','肖义')
​
​
--20.查询不是“计算机系”学院的所有学生(not in 关键字查询)
select * from StudentInfo where ClassId not in
(
select Id from ClassInfo where College='计算机系'
)
​
​
--查询成绩比学生编号为'180325011','180325012'其中一位高的同学
-- any,some:某一个，其中一个  
select * from StudentInfo where stuId in
(
select stuId from StudentScore where skillScore> 
    some
(
select skillScore from StudentScore where stuId in('180325011','180325012')
)
)
​
​
--查询成绩比学生编号为'180325011','180325012'都高的同学（all）
-- all:所有
​
select * from StudentInfo where stuId in
(
select stuId from StudentScore where skillScore> 
all
(
select skillScore from StudentScore where stuId in('180325011','180325012')
)
)
​
​
--Row_Number() Over(Order by 字段):按某个字段进行编号排名
-- 以stuId进行升序排名
select Row_Number() Over(Order by stuId)  ,* from StudentInfo
​
​
​
-- 按总成绩降序排序并给每一位同学进行编号
select Row_Number() Over(Order by skillScore desc) as 排名, * from StudentScore 
​
​
-- 按总成绩降序排序后查询4-8名的学生信息
select * from(
select Row_Number() Over(Order by (skillScore+theoryScore) desc) as 排名, * from StudentScore 
) aa where aa.排名 between 4 and 8
​
-- sqlserver 2012以后，offset rows  fetch next rows only
-- offset:在。。。位置 
select * from StudentScore order by (skillScore+theoryScore) desc offset 3 rows fetch next 5 rows only
​
​
-- 获取按Id排序后的第3-5位同学信息
select * from(
select Row_Number() Over(Order by StuId) as 排名, * from StudentScore 
) aa where aa.排名 between 3 and 5
​
​
-- 
select * from StudentScore order by Id offset 2 rows fetch next 3 rows only
​