--===========================
--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
※涉及到的学生信息全部由本人自己假定,若有同名者,属巧合
分享到:
相关推荐
SQL SERVER的CTE用法,包括列值计算等
SQLserver CTE With 使用with产出临时表,在根据临时表二次查询
SQL Server 2005 杂谈 使用 公用表表达式 CTE 简化 嵌套 SQL SQL Server 2005 杂谈 使用 公用表表达式 CTE 简化 嵌套 SQL
本书是Inside Microsoft SQL Server 2005系列四本著作中的一本。它详细介绍了T-SQL的内部体系结构,包含了非常全面的编程参考,提供了使用Transact-SQL(T-SQL)的专家级指导,囊括了非常全面的编程参考,揭示了基于...
详细描述在sqlserver 2005 中通过使用CTE 实现递归的方法
在当的地方增加适当的索引并从不合理的地方删除次优的索引,将有助于优化那些性能较差的SQL Server应用。实践表明,合理的索引设计是建立在对各种查询的分析和预测上的,只有正确地使索引与程序结合起来,才能产生...
递归CTE是SQL SERVER 2005中重要的增强之一。一般我们在处理树,图和层次结构的问题时需要用到递归查询...本文给大家详解使用sqlserver cte递归查询出来树、图和层次结构,本文介绍的非常详细,感兴趣的朋友一起看看吧
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是Microsoft SQL Server 2008系列中的一本。书中全面深入地介绍了T-SQL的基本元素,以及SQL Server 2008中新增加的一些特性。主要包括SQL的基础理论、逻辑...
SQL Server 2005 杂谈 公用表表达式 CTE 递归调用 doc SQL Server 2005 杂谈 公用表表达式 CTE 递归调用 doc
insert into dbo.hierarchy values(1,0,'河南省') ,(2,1,
《SQL Server 2008高级程序设计》由世界顶尖SQL Server权威专家Robert Vieira编写,旨在指导您熟练运用一系列日趋复杂的功能,助您更高效地管理数据。 本书首先介绍SQL Server 2008的新功能,然后在更详实的示例...
SQL Server 2008高级程序设计 4/6 SQL Server 2008 2010 高级程序设计 作者:(美)维埃拉 著,杨华,腾灵灵 译 出版社:清华大学 出版日期:2010-4-1 ISBN:9787302222729 字数:1250000 页码:730 ----------------...
《Microsoft SQL Server 2008技术内幕:T-SQL语言基础》是Microsoft SQL Server 2008系列中的一本。书中全面深入地介绍了T-SQL的基本元素,以及SQL Server 2008中新增加的一些特性。主要包括SQL的基础理论、逻辑...
Chapter 1, Microsoft SQL Server Database Design Principles, explains the database design process and the architecture and working of the SQL Server 2014 Storage Engine. This chapter covers the ...
SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,CTE即公用表表达式 公用表表达式(CTE),是一个在查询中定义的临时命名结果集将在from子句中使用它。每个CTE仅被定义一次(但在其作用域内可以被引用任意...
之前曾有一篇POST是关于用CTE实现Split,这种方法已经比传统的方法高效了。今天我们就这个方法与CLR实现的Split做比较。在CLR实现Split函数的确很简单,dotnet framework本身就有这个function了。
主要介绍了SQL Server 公用表表达式(CTE)实现递归的方法,需要的朋友可以参考下
《SQL Server 2008高级程序设计》由世界顶尖SQL Server权威专家Robert Vieira编写,旨在指导您熟练运用一系列日趋复杂的功能,助您更高效地管理数据。 本书首先介绍SQL Server 2008的新功能,然后在更详实的示例...
《SQL Server 2008高级程序设计》由世界顶尖SQL Server权威专家Robert Vieira编写,旨在指导您熟练运用一系列日趋复杂的功能,助您更高效地管理数据。 本书首先介绍SQL Server 2008的新功能,然后在更详实的示例...