MSSQL常用查询写法2

----
--
--
---------------------------
--构造用例
---------------------------
declare @Student table(ID int,sSex char(2),sName varchar(25))
--学生表 (ID 学生ID、唯一值，sSex 性别,sName 学生姓名)

insert @Student 

select 1,'女','张三' union all
select 2,'男','李四' union all
select 3,'男','王二' union all
select 4,'男','任我行' union all
select 5,'女','长恨歌' 

declare @Subject table(id int,sName varchar(25))
--科目表 (ID 科目ID、唯一值,sName 科目名称)

insert into @Subject

select 1,'语文' union all
select 2,'数学' union all
select 3,'英语' union all
select 4,'物理' union all
select 5,'计算机' 

declare @Score  table (StudentID int,SubjectID int, Score numeric(24,10))
--成绩表(StudentID 学生ID,SubjectID 科目ID,Score 成绩)

insert into @Score

select 1,1,85 union all
select 1,2,77 union all
select 1,3,84 union all
select 1,4,90 union all
select 1,5,91 union all
select 2,1,89 union all
select 2,2,87 union all
select 2,3,68 union all
select 2,4,99 union all
select 2,5,54 union all
select 3,1,95 union all
select 3,3,68 union all
select 3,5,93 union all
select 4,1,66 union all
select 4,2,69 union all
select 4,4,78 union all
select 4,5,80 union all
select 5,1,55 union all
select 5,2,98 union all
select 5,3,86 union all
select 5,4,60 union all
select 5,5,71 

--------------------------
--构造完毕
--------------------------

--1.查询出所有学生的所有科目成绩  不包含缺考的科目
--  姓名、性别、科目、成绩
--如 张三 女    语文  85
select 
    a.sname 姓名,
    a.ssex 性别,
    b.sname 科目,
    c.score 成绩 
from 
    score c join student a on a.id = c.studentid
            join subject b on b.id = c.subjectid    

--2.查询出所有学生的所有科目的成绩 若缺考的科目成绩以NULL代替
--  姓名、性别、科目、成绩
--如 张三 女    语文  85

select 
    a.username 姓名,
    a.ssex 性别,
    a.sname 科目,
    b.score 成绩
from
(
    select 
        a.id as studentid,
        a.sname as username,
        a.ssex,
        b.id as subjectid,
        b.sname 
    from 
        student a,subject b
) a
left join score b on a.studentid = b.studentid and a.subjectid = b.subjectid

--3.查询出没有及格的学生和科目  60分及格
--  姓名、性别、科目、成绩
--如 张三 女    语文  85

select 
    a.username 姓名,
    a.ssex 性别,
    a.sname 科目,
    b.score 成绩
from
(
    select 
        a.id as studentid,
        a.sname as username,
        a.ssex,
        b.id as subjectid,
        b.sname 
    from 
        student a,subject b
) a
left join score b on a.studentid = b.studentid and a.subjectid = b.subjectid
where b.score < 60 or b.score is null

--4.查询出有科目没有及格的学生的所有科目成绩
--  姓名、性别、科目、成绩
--如 张三 女    语文  85

select 
    a.username 姓名,
    a.ssex 性别,
    a.sname 科目,
    b.score 成绩
from
(
    select 
        a.id as studentid,
        a.sname as username,
        a.ssex,
        b.id as subjectid,
        b.sname 
    from 
        student a,subject b
) a
left join score b on a.studentid = b.studentid and a.subjectid = b.subjectid

where
    a.studentid in
(
    select 
        distinct a.studentid
    from
    (
        select 
            a.id as studentid,
            a.sname as username,
            a.ssex,
            b.id as subjectid,
            b.sname 
        from 
            student a,subject b
    ) a
    left join score b on a.studentid = b.studentid and a.subjectid = b.subjectid
    where b.score < 60 or b.score is null
)

---第二种方法

select 
    username 姓名,
    ssex 性别,
    sname 科目,
    score 成绩 
from

(
    select 
        a.studentid,
        a.username,
        a.ssex,
        a.sname,
        b.score
    from
    (
        select 
            a.id as studentid,
            a.sname as username,
            a.ssex,
            b.id as subjectid,
            b.sname 
        from 
            student a,subject b
    ) a
    left join score b on a.studentid = b.studentid and a.subjectid = b.subjectid
) a
right join 
(
select 
    distinct a.studentid
from
(
    select 
        a.id as studentid,
        a.sname as username,
        a.ssex,
        b.id as subjectid,
        b.sname 
    from 
        student a,subject b
) a
left join score b on a.studentid = b.studentid and a.subjectid = b.subjectid
where b.score < 60 or b.score is null
) b 
on a.studentid = b.studentid

--5。列出每个学生的平均分 
--  姓名 性别 最高分 最低分 平均分 参考科目数 --如有缺考该科则算平均分时，该科算0分，参考科目数不统该科

select 
    a.sname 姓名,
    a.ssex 性别,
    max(c.score) 最高分,
    min(c.score) 最低分,
    avg(c.score) 平均分,
    count(b.sname) 参考科目数  
from 
    score c join student a on a.id = c.studentid
            join subject b on b.id = c.subjectid
group by a.sname,a.ssex

--6.列出每科最高分的学生
--  姓名、性别、科目、成绩
--如 张三 女    语文  85

select 
    sname,
    ssex,
    subname,
    a.score
from
(
    select 
        max(c.score) score
    from 
        score c join student a on a.id = c.studentid
                join subject b on b.id = c.subjectid
    group by c.subjectid
) a
left join 
(
    select 
        a.sname,
        a.ssex,
        b.sname subname,
        c.score 
    from 
        score c join student a on a.id = c.studentid
                join subject b on b.id = c.subjectid
) b
on a.score = b.score

--7.列出每科的最高分 最低分 平均分  参考人数
--  科目 最高分 最低分 平均分  参考人数  -有缺考该科则算平均分时，该科算0分，参考科目数不统该科

select 
    b.sname 科目,
    max(c.score) 最高分, 
    min(c.score) 最低分,
    avg(c.score) 平均成绩,
    count(b.id) 参考人数
from 
    score c join student a on a.id = c.studentid
            join subject b on b.id = c.subjectid
group by b.sname
  

--8.查询出该科成绩大于该科平均分的学生和科目
--  姓名、性别、科目、成绩
--如 张三 女    语文  85

select
    sname 姓名,
    ssex 性别,
    subname 科目,
    score 成绩
from
(
    select 
        b.id,
        avg(c.score) avgscore
    from 
        score c join student a on a.id = c.studentid
                join subject b on b.id = c.subjectid
    group by b.id
) a
right join 
(
    select 
        a.sname,
        a.ssex,
        b.id,
        b.sname subname,
        c.score 
    from 
        score c join student a on a.id = c.studentid
                join subject b on b.id = c.subjectid
) b
on a.id = b.id
where a.avgscore < b.score

--9.列出所有学生中总分排名
--  姓名、性别、总分,排名

select 
    sname 姓名,
    ssex 性别,
    总分,
    名次 = (
        select count(*)+1 from 
            (
            select 
                a.sname,
                a.ssex,
                sum(c.score) 总分
            from 
                score c join student a on a.id = c.studentid
                        join subject b on b.id = c.subjectid
            group by a.sname,a.ssex
            ) a
            where a.总分>b.总分
        )
from
(
select 
    a.sname,
    a.ssex,
    sum(c.score) 总分
from 
    score c join student a on a.id = c.studentid
            join subject b on b.id = c.subjectid
group by a.sname,a.ssex
) b

--第二种方法

declare @Place table(姓名 varchar(25),性别 char(2),总分 numeric(24,10),名次 int identity(1,1))
insert @Place(姓名,性别,总分)
select 
    a.sname,
    a.ssex,
    sum(c.score)
from 
    score c join student a on a.id = c.studentid
            join subject b on b.id = c.subjectid
group by a.sname,a.ssex
order by sum(c.score) desc
select * from @Place

--10.列出有人缺考的科目
--科目  

select 
    distinct a.sname 科目
from
(
    select 
        a.id as studentid,
        b.id as subjectid,
        b.sname 
    from 
        student a,subject b
) a
left join score b on a.studentid = b.studentid and a.subjectid = b.subjectid
where b.score is null

--可以试试10题中 再列出参考人数

select 
    a.sname 科目,
    count(a.studentid) 参考人数
from
(
    select 
        a.id studentid,
        b.id subjectid,
        b.sname
    from 
        score c join student a on a.id = c.studentid
                join subject b on b.id = c.subjectid
) a
right join 
(
    select 
        distinct a.subjectid
    from
    (
        select 
            a.id as studentid,
            b.id as subjectid
        from 
            student a,subject b
    ) a
    left join score b on a.studentid = b.studentid and a.subjectid = b.subjectid
    where b.score is null
) b
on a.subjectid = b.subjectid
group by a.sname