Sql学习第三天——SQL 关于CTE(公用表达式)的递归查询使用
关于使用CTE(公用表表达式)的递归查询----SQL Server 2005及以上版本, 公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。, 当某个查询引用递归 CTE 时，它即被称为递归查询。递归查询通经常用的于返回分层数据，例如：显示某个组织图中的雇员或物料清单方案（其中父级产品有一个或多个组件，而那些组件可能还有子组件，或者是其他父级, 递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 数据库语句中运行递归查询所需的具体相关代码。在 SQL Server 的早期版本中，递归查询通,CTE 的基本语法结构如下：,复制具体相关代码 具体相关代码如下:, WITh expression_name [ ( column_name [,...n] ) ] AS ( CTE_query_definition ) --只有在查询,CTE后面必须直接跟使用CTE的SQL数据库语句（如select、insert、update等），否则，CTE将失效。如下面的SQL数据库语句将无法正常使用CTE：,复制具体相关代码 具体相关代码如下:, with cr as （ select * from 表名 where 条件 ） --select * from person.CountryRegion --假如如果加上这句话,复制具体相关代码 具体相关代码如下:,with cte1 as ( select * from table1 where name like '测试%' ), cte2 as ( select * from table2 where id,4. CTE 可以引用自身，也可以引用在同一 WITh 子句中预先定义的 CTE。,5. 不能在 CTE_query_definition 中使用以下子句：,复制具体相关代码 具体相关代码如下:,COMPUTE 或 COMPUTE BY ORDER BY（除非指定了 TOP 子句） INTO 带有查询提示的 OPTION 子句 FOR XML FOR BROWSE,复制具体相关代码 具体相关代码如下:,declare @s nvarchar(3) set @s = '测试%'; -- 必须加分号 with t_tree as ( select * from 表 where 字段 like @s ) ,上面可能对with as说的有点儿啰嗦了，下面进入正题：,老规矩先建表(Co_ItemNameSet):,复制具体相关代码 具体相关代码如下:,CREATE TABLE [dbo].[Co_ItemNameSet]( [ItemId] [int] NULL, [ParentItemId] [int] NULL, [ItemName] [nch,复制具体相关代码 具体相关代码如下:,--给表插入数据 insert into dbo.Co_ItemNameSet values(2,0,'管理费用') insert into dbo.Co_ItemNameSet values(3,0,复制具体相关代码 具体相关代码如下:,--查询数据 select * from Co_ItemNameSet,题目需求是：查询ItemId=2及子节点，也就是管理费用和其下属所有节点的信息,操作办法1：先看看不用CTE递归操作办法的sql数据库语句如下（需要真是的建两个表进行数据的存放和判断，非常麻烦）：,复制具体相关代码 具体相关代码如下:
关于with ties
对于with ties一般是和Top , order by相结合使用的,会查询出最后一条数据额外的返回值（解释：假如如果按照order by 参数排序TOP n（PERCENT）返回了前面n（pencent）个记录，但是n 1…n k条记录和排序后的第n条记录的参数值（order by 后面的参数）相同，则n 1、…、n k也返回。n 1、…、n k就是额外的返回值）。
实验：
实验用表（PeopleInfo）：
复制具体相关代码 具体相关代码如下:
CREATE TABLE [dbo].[PeopleInfo](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[numb] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[phone] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
向表中插入数据：
复制具体相关代码 具体相关代码如下:
insert into peopleinfo([name],numb,phone) values ('李欢','3223','1365255')
insert into peopleinfo([name],numb,phone) values ('李欢','322123','1')
insert into peopleinfo([name],numb,phone) values ('李名','3213112352','13152')
insert into peopleinfo([name],numb,phone) values ('李名','32132312','13342563')
查看插入的全部数据：
复制具体相关代码 具体相关代码如下:
select * from dbo.PeopleInfo
结果图：
操作办法步骤1：不用with ties
具体相关代码：
复制具体相关代码 具体相关代码如下:
select top 3 * from peopleinfo order by [name] desc
结果如图：
操作办法步骤2：用with ties
具体相关代码：
复制具体相关代码 具体相关代码如下:
select top 3 with ties * from peopleinfo order by [name] desc
结果如图：
假如如果with ties不与top和order by结合使用的错误示范：
操作办法步骤1：不与order by结合使用，只和top结合使用：
具体相关代码：
复制具体相关代码 具体相关代码如下:
select top 3 with ties * from peopleinfo
错误消息如图：
操作办法步骤2：不与top结合使用，只和order by结合使用：
具体相关代码：
复制具体相关代码 具体相关代码如下:
select with ties * from peopleinfo order by [name] desc
错误消息如图：
操作办法步骤3：不与top结合使用也不与order by结合使用：
具体相关代码：
复制具体相关代码 具体相关代码如下:
select with ties * from peopleinfo
错误消息如图：
