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 龙文 计算机科学与技术 男20442
12082519 罗莉 计算机科学与技术 女22 402
12082502 华敏 计算机科学与技术 女21385
12082511 刘霞 计算机科学与技术 女30361
12082601 黄林玲 信息与计算科学女24348
/*
-----------------------------------------------------------------------------------------------------------
你可以视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
※涉及到的学生信息全部由本人自己假定，若有同名者，属巧合
页:
[1]