MSSQL常用查询写法1

select * from Student d
select * from Subject b
select * from Score c

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

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

--第一种方法：
--=========================================================
if object_id('tempdb..#temp000') is not null drop table #temp000
--if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[temp000]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--drop table [dbo].[temp000]

create table #temp000(StudentID int,SubjectID int, Score numeric(24,10))

declare @id1 int
declare yb1 cursor for 
    select id from Student
    open yb1 
    fetch Next from yb1 into @id1
  while @@FETCH_STATUS=0    
    begin
        insert into #temp000 
        select @id1,b.id,null from Subject b 
    fetch Next from yb1 into @id1
    end
    CLOSE yb1
    DEALLOCATE yb1
--select * from temp000
update #temp000
    set #temp000.score=c.score
from Score c
    where #temp000.studentid=c.studentid and #temp000.subjectid=c.subjectid

--select d.id,d.ssex,d.sname,b.sname,b.id from Student d,Subject b
--order by d.id
--------------------------------------------------------------------------

select d.sname as dname,d.ssex,b.sname as bsname,c.score from Student d 
left join #temp000 c on d.id=c.studentid
left join Subject b on c.subjectid=b.id
--============================================

--第二种方法：
select n1.dsname,n1.ssex,n1.bsname,c.score from 
    (select d.id as did,d.ssex,d.sname as dsname,b.sname as bsname,b.id as bid from Student d,Subject b)n1
left join Score c on n1.did=c.studentid and n1.bid=c.subjectid

--3.查询出没有及格的学生和科目  60分及格
--  姓名、性别、科目、成绩
--如 张三 女    语文  85
select d.sname as dname,d.ssex,b.sname as bsname,c.score from Student d 
left join score c on d.id=c.studentid
left join Subject b on c.subjectid=b.id
where c.score<60

--4.查询出有科目没有及格的学生的所有科目成绩
--  姓名、性别、科目、成绩
--如 张三 女    语文  85
select n1.dname,n1.ssex,n1.bsname,n1.score  from 
(
    select d.id,d.sname as dname,d.ssex,b.sname as bsname,c.score from Student d 
    left join score c on d.id=c.studentid
    left join Subject b on c.subjectid=b.id
)n1,
(
    select d.id from Student d 
    left join score c on d.id=c.studentid
    where c.score<60
)n2
where n1.id=n2.id

--5。列出每个学生的平均分 
--  姓名 性别 最高分 最低分 平均分 参考科目数 --如有缺考该科则算平均分时，该科算0分，参考科目数不统该科
select n1.studentid,d.sname,d.ssex,n1.mxfs,n1.mnfs,n2.pjf,n2.ckkms from
    (select studentid,max(score) as mxfs,min(score) as mnfs from Score
        group by studentid)n1
inner join
    (select studentid,sum(score) as zfs,count(*) as ckkms,sum(score)/count(*) as pjf from Score
        group by studentid)n2
    on n1.studentid=n2.studentid
left join Student d on n1.studentid=d.id

--第二种方法
select n1.studentid,d.sname,d.ssex,n1.mxfs,n1.mnfs,n1.pjf,n1.ckkms from
    (select studentid,max(score) as mxfs,min(score) as mnfs,sum(score) as zfs,count(*) as ckkms,sum(score)/count(*) as pjf from Score
        group by studentid)n1
left join Student d on n1.studentid=d.id

--第三种方法
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 d.sname as dname,d.ssex,b.sname as bsname,c.score from Student d 
inner join (select c.* from Score c,
    (select c.subjectid,max(c.score) as score from Score c 
        group by c.subjectid)n1
    where c.subjectid=n1.subjectid and c.score=n1.score)c on d.id=c.studentid
left join Subject b on c.subjectid=b.id

--7.列出每科的最高分 最低分 平均分  参考人数
--  科目 最高分 最低分 平均分  参考人数  -有缺考该科则算平均分时，该科算0分，参考科目数不统该科
select n1.subjectid,b.sname,n1.mxfs,n1.mnfs,n2.pjf,n2.ckkms from
    (select subjectid,max(score) as mxfs,min(score) as mnfs from Score
        group by subjectid)n1
inner join
    (select subjectid,sum(score) as zfs,count(*) as ckkms,sum(score)/count(*) as pjf from Score
        group by subjectid)n2
    on n1.subjectid=n2.subjectid
left join Subject b on n1.subjectid=b.id 

--8.查询出该科成绩大于该科平均分的学生和科目
--  姓名、性别、科目、成绩
--如 张三 女    语文  85
select d.sname as dname,d.ssex,b.sname as bsname,c.score from Student d 
left join (select c.studentid,c.subjectid,c.score from Score c,
    (select subjectid,sum(score) as zfs,count(*) as ckkms,sum(score)/count(*) as pjf from Score
        group by subjectid)s
    where c.subjectid=s.subjectid and c.score>s.pjf) c on d.id=c.studentid
left join Subject b on c.subjectid=b.id

--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

--第二种方法：
if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[temp111]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp111]

CREATE TABLE [dbo].[temp111] (
    [noId] [int] IDENTITY (1, 1) NOT NULL ,
    [sname] [varchar] (50) COLLATE Chinese_PRC_CI_AS ,
    [ssex] [varchar] (50) COLLATE Chinese_PRC_CI_AS ,
    [score] [int] 
) ON [PRIMARY]

insert temp111
select d.sname,d.ssex,c.score from 
    (select c.studentid,sum(c.score) as score from Score c 
        group by c.studentid)c
    ,Student d
where c.studentid=d.id
order by c.score desc

select sname,ssex,score,noid from temp111

--10.列出有人缺考的科目
--科目  
select b.id,b.sname,n2.qkrs from Subject b
inner join 
    (select subjectid from #temp000
        where score is null)n1
    on b.id=n1.subjectid
left join 
    (select subjectid,count(*) as qkrs from #temp000
        where score is null
        group by subjectid)n2
    on n2.subjectid=n1.subjectid

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

if object_id('tempdb..#temp000') is not null drop table #temp000
--if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[#temp000]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--drop table [dbo].[#temp000]
if exists (select 1 from dbo.sysobjects where id = object_id(N'[dbo].[temp111]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[temp111]