SQL Server CTE

--===========================
--author:_yeeXun
--date:发表于 @2010年11月18日　10:16:00
--address:Jau 17-304
--===========================
--topic:CTE(common table expression)
--===========================
use 信息技术学院
go

with 学生综合表现(学号,姓名,专业,性别,年龄,总成绩)
as
(
    select a.学号,a.姓名,a.专业,a.性别,datediff(year,a.出生日期,getdate()) as 年龄, sum(b.成绩)
    from 学生信息 a inner join 选课 b
    on a.学号=b.学号
    group by a.学号,a.专业,a.姓名,a.性别,a.出生日期
)

select top 5 *  from 学生综合表现
order by 总成绩 desc
go

 

结果
(5 行受影响)
学号        姓名    专业               性别     年龄   总成绩
--------------------------------------------------------------
12082512    龙文    计算机科学与技术    男        20       442
12082519    罗莉    计算机科学与技术    女        22     402
12082502    华敏    计算机科学与技术    女        21       385
12082511    刘霞    计算机科学与技术    女      30       361
12082601    黄林玲    信息与计算科学        女        24       348
/*

-----------------------------------------------------------------------------------------------------------
你可以视CTE为字select、insert、update、delete、create view表达式的执行范围内定义的临时结果集
CTE有点类似于派生表：他们都不存储为数据库对象，即他们的生命周期只限于查询期间；
CTE与派生表的区别：CTE可以自我引用(“递归公用表达式”)，而且可以在同一查询中多次引用
CTE的用途：
1.建立递归查询
2.用来替代视图。视图为数据库对象，适合经常性和例行性的用途；除此之外，用CTE比较适合
3.根据派生自标量子查询(或派生自不确定性或具有外部访问的函数)的字段来分组
4.在相同的表达式中引用结果表多次
5.借助CTE，可以将整个查询切割成不同的区块，让复杂的查询，变得简单

CTE可用于用户定义函数、存储过程、触发器和视图
语法：
with expression_name(column_name)
as
(CTE_query_definition)
expression_name：CTE的名称，因为可以在with表达式中定义多个CTE，所以此名称必须唯一
column_name：CTE的字段名称，不能重复，而且必须与CTE_query_definition的结果集中的数目完全相同
CTE_query_definition：一个select表达式

CTE遵循原则
1.在定义CTE的with表达式之后，必须紧跟着引用insert、update或delete表达式；在create view表达式中，CTE也可以是用于定义视图的select表达式的一部分
2.可以在非递归的CTE中，定义多个CTE查询定义。这些定义必须使用union all、all、union、intersect或except运算符来合并
3.CTE可以引用其本身
4.不可以在CTE_query_definition中使用一下子句：
    ·compute或compute by
    ·order by(除非指定了top子句)
    ·into
    ·含查询提示的option子句
    ·for xml
    ·for browse
5.当批处理中的表达式使用CTE时，在CTE之前的表达式后面必须紧跟着分号
6.引用CTE的查询可用来定义数据指针
7.可以在CTE中引用远程服务器的表

----------------------------------------------------------------------------------------------------------------------
*/

查询出选每一门课程的人数
with 选课人数(课程,人数)
as
(
    select a.课程名,count(*)
    from  课程 a inner join 选课 b
    on a.课程号=b.课程号
    group by a.课程名
)
select 课程,人数 as 选课人数 from 选课人数

 

结果
(17 行受影响)
课程              选课人数
------------------------------
ASP.NET程序设计           2
JSP程序设计               3
Matlab教程               2
VC++程序设计           3
编译原理               3
操作系统               2
计算机网络               5
计算机专业英语           4
密码编码学与网络安全   5
数据库系统概论           2
数学模型               5
数字信号               1
算法分析与设计           4
通信原理               2
网页设计               2
微机原理与接口技术       4
自动控制原理           2
------------------------------------------------

查询出每一门课程的最高分，最低分

with 科目成绩(课程名,最高分,最低分)
as
(
    select a.课程名,max(b.成绩) as 最高分,min(b.成绩) as 最低分
    from 课程 a inner join 选课 b
    on a.课程号=b.课程号
    group by a.课程名
)

select * from 科目成绩
order by 最高分 desc

 

结果
(17 行受影响)
课程名       最高分     最低分
----------------------------------------------------
编译原理            95    74
微机原理与接口技术    92    64
数学模型            91    56
JSP程序设计            91    78
计算机专业英语        91    56
计算机网络            89    60
Matlab教程            89    77
自动控制原理        89    69
算法分析与设计        88    58
通信原理            88    50
ASP.NET程序设计        88    77
密码编码学与网络安全88    74
VC++程序设计        86    77
网页设计            86    56
数字信号            85    85
数据库系统概论        85    75
操作系统            78    59
go

※涉及到的学生信息全部由本人自己假定，若有同名者，属巧合