sql server 2005开始推出了公用表表达式 (CTE),这个表达式是个人觉得挺有用的。
我主要是用于树结构的递归查询和简化sql语句增加可读性和可维护性。
公用表表达式其实提供的功能和视图差不多,但是它不像视图一样把sql语句保存在我们的数据库里面。虽然CTE不是必需的,但是它可以为提高sql的可读性。微软官方给的使用CET的优势:
- 编写一个递归查询(类似树查询)
- 使用要使用一个类似视图的功能,但是又不想把这个查询sql语句的定义保存在数据库
- 要引用一个返回数据sql语句多次,只需要定义一次。
由于业务需要,我们经常会写一些比较复杂的sql语句,里面会有许多的join或者子查询,要维护和理清这种n多个表的join关系一件非常头疼的事。使用cet可以使维护和理解复杂的sql语句可以更加的容易。
在开发的时候使用子查询时,一般是这种情况:需要从一个复杂的子查询,甚至多级子查询嵌套。在这种情况下,在整个sql语句里面,无论你是直接写sql语句还是把这段sql语句包成子查询然后有别名来访问,当业务需求越来越变的复杂,你将在随时在修改这个大且复杂sql语句,维护这种复杂的、可读性差的sql语句简直是一个噩梦。庆幸是我们可以用cet,用cet可以定义一个sql语句一次,且为这这个sql定义一个别名,接下来就通过别名来引用这个定义sql返回的数据,就像使用普通表一样。
公用表表达式 (CTE)语法:
一个公用表表达式 (CTE)有三个主要部分:
- CET名称(With后面,列名列之前)
- 列名列(可选)
- CET查询语句主体(AS后面括起来的内容)
1、用cet简化树的查询
在实际的开发中分类一般都是树的结构,下面我通过一个具体的例子来说明cet在递归中的用法。创建表结构和插入数据
- CREATE TABLE Category
- (
- ID INT PRIMARY KEY,
- CateName VARCHAR(50),
- ParentID INT
- )
- INSERT INTO Category
- (
- ID,
- CateName,
- ParentID
- )
- SELECT 1,'文科',0
- UNION ALL SELECT 2,'理科',0
- UNION ALL SELECT 3,'历史',1
- UNION ALL SELECT 4,'地理',1
- UNION ALL SELECT 5,'政治',1
- UNION ALL SELECT 6,'化学',2
- UNION ALL SELECT 7,'生物',2
- UNION ALL SELECT 8,'物理',2
- UNION ALL SELECT 9,'中国近代史',3
- UNION ALL SELECT 10,'中国近代史10',9
查询某个结点的所有子结点信息
- WITH a_cet(ID,Level)
- AS
- (
- SELECT ID,2 as Level FROM Category c WHERE c.ID=3
- UNION all
- SELECT a.ID,b.[Level]+1 FROM Category a,a_cet b WHERE a.ParentID=b.ID
- )
- SELECT a.*,b.[Level] FROM Category a,a_cet b WHERE a.ID=b.ID
执行结果
2、一次定义多次引用
有时一个比较长的sql语句有相同的sql部分要使用多次,就可以定义有cet。直接引用就可以了,这样sql语句就简短了许多,可维护性也大大的提高了。例如
- WITH root_cet(ID)
- AS
- (
- SELECT ID from Category WHERE ParentID=0
- )
- SELECT * FROM root_cet
- SELECT * FROM Course WHERE CateID IN (SELECT ID FROM root_cet)
3、使用CTE时应注意事项
1. CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则,CTE将失效。如下面的语句会报错:
- WITH root_cet(ID)
- AS
- (
- SELECT ID from Category WHERE ParentID=0
- )
- SELECT 1
- SELECT * FROM root_cet
定义了公用表表达式,但没有使用。
2、 CTE后面也可以跟其他的CTE,但只能使用一个with,多个CTE中间用逗号(,)分隔,如下面的SQL语句所示:
- WITH a_cet(ID,Level)
- AS
- (
- SELECT ID,2 as Level FROM Category c WHERE c.ID=3
- UNION all
- SELECT a.ID,b.[Level]+1 FROM Category a,a_cet b WHERE a.ParentID=b.ID
- ),
- root_cet(ID)
- AS
- (
- SELECT ID from Category WHERE ParentID=0
- )
- SELECT a.*,b.[Level] FROM Category a,a_cet b WHERE a.ID=b.ID
3. 如果CTE的表达式名称与某个数据表或视图重名,则紧跟在该CTE后面的SQL语句使用的仍然是CTE,当然,后面的SQL语句
使用的就是数据表或视图了。
4. CTE 可以引用自身,也可以引用在同一WITH 子句中预先定义的CTE。不允许前向引用。
5. 不能在CTE_query_definition 中使用以下子句:
(1)COMPUTE 或COMPUTE BY(2)ORDER BY(除非指定了TOP 子句)
(3)INTO
(4)带有查询提示的OPTION 子句
(5)FOR XML
(6)FOR BROWSE
6. 如果将CTE 用在属于批处理的一部分的语句中,那么在它之前的语句必须以分号结尾
- DECLARE @ID int
- SET @ID=0;--必须要有分号结尾
- WITH root_cet(ID)
- AS
- (
- SELECT ID from Category WHERE ParentID=@ID
- )
- SELECT * FROM root_cetsql