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