SQL Server数据库开发（3.SQL高级查询）

一，嵌套子查询

        降低SQL语句的复杂度，提高SQL语句的可读性

--子查询作为条件  (where)
--查询王五前面的同学
select * from StuInfo where stuid < (select stuid from StuInfo where stuName = '王五')
go

--子查询作为临时表  (from)
--查询李四成绩大于80分(StuInfo作为子查询)
select stuName,subject,score from StuMarks s1,
(select * from StuInfo where stuName = '李四') s2
where s1.stuid = s2.stuid and score > 80
go

--(StuMarks作为子查询)
select stuName,subject,score from StuInfo s1,
(select * from StuMarks where score > 80) s2
where s1.stuid = s2.stuid and stuName = '李四'

--子查询作为列   (select)
select s.*,
(select score from StuMarks where subject = 'HTML' and s.stuid = StuMarks.stuid) as '成绩' 
from StuInfo s

 

-in 在...范围之内   not in 不在...范围之内 （意思一样，可以包含多个值，中间用逗号隔开）

--查询学好为123的学生信息
select * from StuInfo where stuid in (1,2,3)

--查询Java成绩在80以上的同学
select * from StuMarks where subject = 'JAVA' and score >80
select stuName from StuInfo s1,(select * from StuMarks where subject = 'JAVA' and score >80) s2
where s1.stuid=s2.stuid
--in方法去写
select stuName from StuInfo where stuid in(select stuid from StuMarks where subject = 'JAVA' and score >80)

--查询所以平均成绩大于80分
select AVG(score) from StuMarks group by stuid having AVG(score)>80
select * from StuInfo where stuid in (select AVG(score) from StuMarks group by stuid having AVG(score)>80) 

--exists    not exists (是否存在)
select * from StuInfo where exists 
(select * from StuMarks where StuInfo.stuid=StuMarks.stuid)

SOME、 ANY、 ALL后必须跟子查询
        --SOME 和 ANY 的作用是一样的,表示其中的任何一项 
        select * from StuInfo where stuid > any(select stuid from StuInfo where stuid>1)
        select * from StuInfo where stuid > some(select stuid from StuInfo where stuid>1)
        --all表示其中的所有的项
        select * from StuInfo where stuid > all(select stuid from StuInfo where stuid>1)

二，聚合技术

--compute  by
select StuInfo.stuid,stuName,stusex,[subject],score from StuInfo,StuMarks where StuInfo.stuid = StuMarks.stuid order by StuInfo.stuid
compute max(score),min(score),avg(score) by stuid

三，排序函数

ROW_NUMBER根据排序语句,递增排序
select ROW_NUMBER()over(order by AVG(score) desc ) as '名次',stuid,AVG(score) from StuMarks  GROUP BY stuid
--rank根据排序语句,但是存在并列,跳空
select rank()over(order by AVG(score) desc ) as '名次',stuid,AVG(score) from StuMarks  GROUP BY stuid
--dense_rank根据排序语句,但是存在并列,不跳空
select dense_rank()over(order by AVG(score) desc ) as '名次',stuid,AVG(score) from StuMarks  GROUP BY stuid
--partition by分组列
select DENSE_RANK() over(partition by subject order by score desc)as'排名',StuInfo.stuid,stuName,[subject] from StuInfo,StuMarks where StuInfo.stuid=StuMarks.stuid

select DENSE_RANK() over(partition by subject order by score desc)as'排名',[subject],score from StuInfo,StuMarks where StuInfo.stuid = StuMarks.stuid

四，公示表表达式

with sumscore(学号,总成绩)
as
(
    select stuid,SUM(score)
    from StuMarks
    group by stuid
)    
select * from sumscore

 

with sscore(学号,姓名,总成绩)
as
(
    select StuInfo.stuid,stuname,SUM(score)
    from StuMarks,StuInfo
    where StuInfo.stuid=StuMarks.stuid
    group by StuInfo.stuid,stuname
)    
select * from sscore