Sql Server2005 Transact-SQL 新兵器学习总结之-公用表表达式(CTE)

第一篇：Sql Server2005 Transact-SQL 新兵器学习总结之-公用表表达式(CTE)

公用表表达式是Sql Server2005新增加的一个非常好用的功能。

公用表表达式(CTE)可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。

CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。

CTE可用于：

1.创建递归查询(我个人认为CTE最好用的地方)2.在同一语句中多次引用生成的表 CTE优点: 使用 CTE 可以获得提高可读性和轻松维护复杂查询的优点。

查询可以分为单独块、简单块、逻辑生成块。之后，这些简单块可用于生成更复杂的临时 CTE，直到生成最终结果集。

CTE可使用的范围：

可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。

下面看一个简单的CTE例题：

把test表中salary最大的id记录保存在test_CTE中,再调用

with test_CTE(id,salary)as(select id ,max(salary)from test group by id)select * from test_cte

由上面例题可以看出：

CTE 由表示 CTE 的表达式名称、可选列列表和定义 CET 的查询组成。

定义 CTE 后，http://mmm.qqq23.com 可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中对其进行引用，就像引用表或视图一样。简单的说CTE可以替代临时表和表变量的功能。

我个人认为cte最好用的地方是创建递归查询，下面演示一下这功能： 现有一数据结构如下：

这些数据存放在表Co_ItemNameSet中,表结构和部分数据如下： ItemId ParentItemId ItemName 2 0 管理费用 3 0 销售费用 4 0 财务费用 5 0 生产成本 35 5 材料 36 5 人工 37 5 制造费用 38 35 原材料 39 35 主要材料 40 35 间辅材料 41 36 工资 42 36 福利 43 36 年奖金

现在需求是:我想查询ItemId=2,也就是管理费用和其下属所有节点的信息 通过CTE可以很简单达到需求要的数据

为了体现CTE的方便性，我特意也写了一个sql2000版本的解决方法，先看看sql2000是怎么解决这个问题的

--sql2000版本 DECLARE @i INT SELECT @i=2;http://www.xiexiebang.com /* 使用临时表作为堆栈来跟踪所有正在处理中的项目（已经开始但尚未结束）。某个项目一旦处理完毕，将被从堆栈中删除。当发现新的项目时，这些项目将被添加到堆栈中。*/

CREATE TABLE #tem([ItemId] [INT] NOT NULL, [level] INT);/* 存放结果 */

CREATE TABLE #list([ItemId] [INT] NOT NULL, [ParentItemId] [INT] NOT NULL DEFAULT((0)), [ItemName] [nvarchar](100)NOT NULL DEFAULT(''), [level] INT);

INSERT INTO #tem([ItemId],[level])SELECT ItemId, 1 FROM Co_ItemNameSet WHERE itemid=@i

INSERT INTO #list([ItemId],[ParentItemId],[ItemName],[level])SELECT ItemId, ParentItemId, ItemName ,1 FROM Co_ItemNameSet WHERE itemid=@i

DECLARE @level INT SELECT @level=1

DECLARE @current INT SELECT @current=0

/* 当 @level 大于 0 时，执行以下步骤：

1.如果当前级别(@level)的堆栈中有项目，就选择其中一个，并称之为 @current。2.从堆栈中删除该项目以免重复处理它，然后将其所有子项目添加到堆栈的下一级(@level + 1)中。

3.如果有子项目(IF @@ROWCOUNT > 0)，则下降一级处理它们(@level = @level + 1)；否则，继续在当前级别上处理。

4.最后，如果在当前级别的堆栈中没有待处理的项目，则返回到上一级，看上一级是否有待处理的项目(@level = @level-1)。当再没有上一级时，则完毕。*/

WHILE(@level>0)BEGIN

SELECT @current=ItemId FROM #tem WHERE [level]=@level

IF @@ROWCOUNT>0

BEGIN

--从堆栈中删除该项目以免重复处理它

DELETE FROM #tem WHERE [level]=@level and ItemId=@current

--将其所有子项目添加到堆栈的下一级(@level + 1)中。

INSERT INTO #tem([ItemId],[level])SELECT [ItemId],@level+

1FROM Co_ItemNameSet WHERE ParentItemId=@current

--将其所有子项目添加

INSERT INTO #list([ItemId],[ParentItemId],[ItemName],[level])SELECT [ItemId],[ParentItemId],[ItemName] ,@level+1

FROM Co_ItemNameSet WHERE ParentItemId=@current

IF @@rowcount>0

BEGIN

SELECT @level=@level+1

END

END

ELSE

BEGIN

SELECT @level=@level-1

END END--显示结果

SELECT * FROM #list

DROP TABLE #tem DROP TABLE #list go

结果如下：

ItemId ParentItemId ItemName level 2 0 管理费用 1 52 2 汽车费用 2 55 2 招聘费 2 56 2 排污费 2 53 52 燃料 3 54 52 轮胎 3

大家看到sql2000解决这个问题比较麻烦，要实现这需求编写的代码比较多，比较复杂 现在好了，在sql2005中通过CTE的递归特点可以2步就实现.得到同样的结果，sql2005的CTE代码简单了许多.这就是CTE支持递归查询的魅力。请看下面的代码：

--sql2005版本 DECLARE @i INT SELECT @i=2;

WITH Co_ItemNameSet_CTE(ItemId, ParentItemId, ItemName,Level)AS(SELECT ItemId, ParentItemId, ItemName ,1 AS [Level]

FROM Co_ItemNameSet WHERE itemid=@i

UNION ALL

SELECT c.ItemId, c.ParentItemId, c.ItemName ,[Level] + 1 FROM Co_ItemNameSet c INNER JOIN Co_ItemNameSet_CTE ct ON c.ParentItemId=ct.ItemId)

SELECT * FROM Co_ItemNameSet_CTE go

Sql Server2005 Transact-SQL 新兵器学习总结之-TOP 运算符

TOP 运算符介绍：

TOP 运算符在Sql Server2005得到了增强，可以使用任何数值表达式（如变量名），而不是仅使用整数来指定该运算符返回的行数。TOP 现在还可以在 INSERT、UPDATE 和 DELETE 语句中指定。TOP 运算符的几个使用总结：

1．TOP 表达式可用在 SELECT、INSERT、UPDATE 和 DELETE 语句中。2．Top表达式可以是常量，变量，子查询

3．取代set rowcount，可以防止锁升级，提高效率

特别提示: 与INSERT、UPDATE 或 DELETE 一起使用的 TOP 表达式中被引用行将不按任何顺序排列。

TOP n 随机返回 n 行。

例如，下面的 INSERT 语句包含 ORDER BY 子句，但该子句并不影响由 INSERT 语句直接引用的行。请看例题：

有1个test表，表结构和表中数据如下： select * from test

id salary manid 100 6500 100 200 5500 200 101 6600 100 102 6200 100 103 5100 100 104 6700 100 201 5800 200 202 4200 200 执行下面的语句：

declare @t table(id int,salary int,manid int)

INSERT TOP(2)INTO @t SELECT id, salary, manid from test ORDER BY salary asc

select * from @t

结果如下：

id salary manid 100 6500 100 200 5500 200

上个查询中的 ORDER BY 子句仅引用嵌套 SELECT 语句返回的行。INSERT 语句选择 SELECT 语句返回的任意两行。

若要确保插入 SELECT 子查询返回的前两行，可以按如下写该查询。

declare @t table(id int,salary int,manid int)

INSERT INTO @t

SELECT TOP(2)id, salary, manid from test ORDER BY salary asc

select * from @t

结果如下：

id salary manid 202 4200 200 103 5100 100

TOP 运算符在Sql Server2005得到了增强,Top表达式可以是常量，变量，子查询

下面我们再来看另个例题：

create table test2([no] int , n nvarchar(100))

insert into test2([no],n)select 1,'a' union select 2,'b' union select 3,'c' union

select 3,'c2' union

select 2,'b2' union

select 2,'b3' go

select * from test2 order by [no] asc go

结果如下： no n 1 a 2 b 2 b2 2 b3 3 c 3 c2

1.利用top变量,筛选no最小的2行

--利用top变量,筛选no最小的2行 declare @i int select @i = 2 select top(@i)* from test2 order by [no] asc go

结果如下： no n 1 a 2 b 我们可以看到,在Sql Server2005中top后面跟的可以是变量了,而在Sql Server2000中必须是常量才可以

要在Sql Server2000实现上述功能比较麻烦，需要用到动态sql语句，请看下面代码：

--Sql Server2000版本 declare @i int select @i = 2

declare @sql nvarchar(255)select @sql=''

select @sql='select top('+ convert(nvarchar(100),@i)+')* from test2 order by [no] asc' exec(@sql)

2.利用top子查询来筛选 select top(select count([no])from test2 where [no]=3)* from test2 order by [no] asc go

结果如下： no n 1 a 2 b

3.WITH TIES参数：

指定从基本结果集中返回更多的行，返回的行与TOP n(PERCENT)行中的最后一行在ORDER BY 列中具有相同的值。

只有在指定ORDER BY 子句之后才能指定TOP WITH TIES。

--返回no前2行，并还返回no=2的行 select top(2)WITH TIES * from test2 order by [no] asc go

第二篇：中小学信息技术“在Excell中制作图表”的讲授新课与归纳总结之学习总结

中小学信息技术“在Excell中制作图表”的讲授新课与归纳总结

学习总结

我听了温晓捷老师主讲的中小学信息技术《“在Excell中制作图表”的讲授新课与归纳总结》。知道了这节课是从课堂教学中的主要环节——教授新课；如何分段掌控课堂教学时间；教学过程与师生互动的对应关系；“总结归纳”教学环节的意义；常用的“总结归纳”形式；“课后作业”的三个功能。六个角度对课堂教学五个环节中的讲授新课、归纳总结和布置课后作业进行了探讨。并且对这节课做了相关的笔记。

然后看了宋东艳老师设计的《 Excel中制作图表》一课，详细的介绍教学的各个环节和总结归纳的形式和意义。本课的教学始终围绕着“图表的作用和图表类型的选择”教学重点展开。通过一环套一环的教学活动及恰当的任务设计，将图表的作用阐述的明白清晰，让学生很清楚地掌握了柱型图、饼图、折线图常用的范围。很好地调动了学生的学习积极性和兴趣，充分说明教师对学生情况十分了解，能依据学生特点，选用适当的教学手段，因此收到了良好的教学效果。从而使自己地温晓捷老师的专题讲座有了进一步的理解。

同时我与一起的学员进入了交流，且回答了“思考与活动”板块的相关问题。总之通过听讲、析案、交流、回答问题，感觉对本讲座的认识提高了一个层次，且提高自己的教育理论水平，让自己的远程培训走入了一新的阶段。