1、公用表表达式在审计数据采集分析中的应用 摘要：随着计算机数据库技术的发展和广泛应用，很多企事业单位的财务、业务数据都实现了数字化管理，同时在日常审计数据采集分析过程中，由于业务需要，我们经常会写一些比较复杂的 SQL查询语句，里面会有许多的 join 或者子查询，要维护和理清这种复杂的查询语句是一件十分头疼的事情；同时还会因为要生成很多中间表而拖慢系统运行速度。如果使用 CTE，一方面可以使维护和理解复杂的 sql语句变得更加容易；另一方面，可以大幅度地提高系统的运行速度。 下载 关键词：数据库；审计；数据分析，公用表表达式； CTE 应用实例 一、CTE 的概念 CTE 的全称是 Commo
2、n Table Expression，即公用表表达式，是在 SQL Server 2005 时才具有的一项新功能。可以认为 CTE 是在单个 SELECT、INSERT、 UPDATE、 DELETE 或 CREATE VIEW 语句查询的临时结果集。 CTE 与派生表类似，即查询结果不存储为对象，它仅仅存在于它发生的语句中，并且只在本次查询期间有效。但与派生表的不同之处在于， CTE 可自引用，还可在同一查询中引用多次。 二、使用 CTE 的好处 使用 CTE 具有实现快速查询，查询语句简单明了，易于维护的优点。当不需要将查询结果集作为表或视图被多个地方引用时，使用 CTE可以使查询更加简洁
3、。 三、 CTE 的使用范围 可以在用户定义的例程（如函数、存储过程、触发器或视图）中定义 CTE。 四、 CTE 语法规则及实例 （一） CTE 语法规则 CTE 的定义非常简单，只包含三部分：公用表表达式名称、所涉及的列名（可选）、一 ?SELECT 语句。其语法结构如下： 需要注意的是： 1.公用表表达式名称：在同一个 WITH子句中定义的 公用表表达式名称不能重复，但可以与源数据表的名称相同。在查询中对公用表表达式名称的任何引用都会使用公用表表达式，而不使用基对象。 2.列名称：在一个 CTE 定义中涉及的列名称可以省略，但不允许出现重复的列名称。指定的列名称、数量必须与 Select
4、 查询结果集中列名称、数量相匹配。只有在 Select 查询定义中为所有结果列都提供了不重复的列名称时，列名称列表才是可选的。 3.SELECT 语句。除了 CTE 不能定义另一个 CTE 以外，查询语句的 SELECT 语句必须满足与创建对象时相同的 要求。 （二） CTE 应用实例 假设某单位财务系统往来科目实行辅助核算，经查原始余额表科目编码结构为 3-2型，往来科目同一科目编码有多条记录。上面的处理过程 3次生成了临时表。这些临时表都存放在系统临时数据库中，如果数据量较大，则会影响系统运行速度，清理起来也比较费时。只有重新启动数据系统时这些临时表才能被清除。 而使用 CTE 公用表表达
5、式，即可解决这一的问题，因为这些步骤不存储为对象（表或视图），它仅仅存在于它发生的语句中，并且只在查询期间有效。 五、使用 CTE 时应注意的问题 （一）如果将 CTE 用在同一个批处理语句中，那么必须用分号将批处理语句与 CTE 分隔开。 （二）如果定义了一个 CTE，那么 CTE 后面必须紧跟要使用的 SQL 语句，如 select、 insert、 update 等，否则所定义的 CTE 将会失效。 （三） with 后面可以定义多个 CTE，但第一个 CTE 后的 CTE 不使用 with，多个 CTE 之间要用逗号 “ ， ” 进行分隔。 （四） CTE 名称不要与数据表名称相同。如
6、果 CTE 的表达式名称与某个数据表重名，则紧跟在该 CTE 后面的 SQL 语句使用的 仍然是 CTE，其后面的SQL 语句使用的才是数据表。因此建议 CTE 命名最好不要与数据表重名，以免混淆。 （五） CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE，但不允许引用另一个 WITH 子句所定义的 CTE（即前向引用）。 （六） CTE 有递归 CTE 和非递归 CTE 之分，使用时应加以区别。 （七）不能在 CTE 表达式中使用 ORDER BY、 INTO 等子句。 参考文献： 晏明春，李酒 .一种新型在线数据库审计系统 J.计算机工程与设计 ，2007（ 05） . 孙国学，马严 .高速网络环境下的快速包捕获技术 J.网络安全技术与应用， 2005（ 10） . 饶元，冯博琴 .基于状态的入侵检测系统研究 J.信息技术， 2003（ 12） . （作者单位：罗平县审计局）
