Sql学习第三天——SQL关于使用CTE(公用表表达式)的递归查询
Sql学习第二天——SQL DML与CTE解释
http://www.2cto.com/database/201303/197233.html
关于使用CTE(公用表表达式)的递归查询----SQL Server 2005及以上版本
公用表表达式 (CTE) 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
当某个查询引用递归 CTE 时,它即被称为递归查询。递归查询通常用于返回分层数据,例如:显示某个组织图中的雇员或物料清单方案(其中父级产品有一个或多个组件,而那些组件可能还有子组件,或者是其他父级产品的组件)中的数据。
递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。在 SQL Server 的早期版本中,递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。
CTE 的基本语法结构如下:
Sql学习第二天——SQL DML与CTE解释
http://www.2cto.com/database/201303/197233.html
关于使用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 的语句为:
SELECT <column_list> FROM expression_name;
在使用CTE时应注意如下几点:
CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则,CTE将失效。如下面的SQL语句将无法正常使用CTE:
CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则,CTE将失效。如下面的SQL语句将无法正常使用CTE:
with
cr as
(
select * from 表名 where 条件
)
--select * from person.CountryRegion --如果加上这句话后面用到cr将报错
select * from cr
cr as
(
select * from 表名 where 条件
)
--select * from person.CountryRegion --如果加上这句话后面用到cr将报错
select * from cr
2. CTE后面也可以跟其他的CTE,但只能使用一个with,多个CTE中间用逗号(,)分隔,如下面的SQL语句所示:
with
cte1 as
(
select * from table1 where name like '测试%'
),
cte2 as
(
select * from table2 where id > 20
),
cte3 as
(
select * from table3 where price < 100
)
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id
3. 如果CTE的表达式名称与某个数据表或视图重名,则紧跟在该CTE后面的SQL语句使用的仍然是CTE,当然,后面的SQL语句使用的就是数据表或视图。
4. CTE 可以引用自身,也可以引用在同一 WITH 子句中预先定义的 CTE。
5. 不能在 CTE_query_definition 中使用以下子句:
COMPUTE 或 COMPUTE BY
ORDER BY(除非指定了 TOP 子句)
INTO
带有查询提示的 OPTION 子句
FOR XML
FOR BROWSE
6. 如果将 CTE 用在属于批处理的一部分的语句中,那么在它之前的语句必须以分号结尾,
如下面的SQL所示:
4. CTE 可以引用自身,也可以引用在同一 WITH 子句中预先定义的 CTE。
5. 不能在 CTE_query_definition 中使用以下子句:
COMPUTE 或 COMPUTE BY
ORDER BY(除非指定了 TOP 子句)
INTO
带有查询提示的 OPTION 子句
FOR XML
FOR BROWSE
6. 如果将 CTE 用在属于批处理的一部分的语句中,那么在它之前的语句必须以分号结尾,
如下面的SQL所示:
declare @s nvarchar(3)
set @s = '测试%'; -- 必须加分号
with
t_tree as
(
select * from 表 where 字段 like @s
)
select * from t_tree
------------------------------------操作------------------------------------
上面可能对with as说的有点儿啰嗦了,下面进入正题:
老规矩先建表(Co_ItemNameSet):
上面可能对with as说的有点儿啰嗦了,下面进入正题:
老规矩先建表(Co_ItemNameSet):
CREATE TABLE [dbo].[Co_ItemNameSet](
[ItemId] [int] NULL,
[ParentItemId] [int] NULL,
[ItemName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
插入数据:
--给表插入数据
insert into dbo.Co_ItemNameSet values(2,0,'管理费用')
insert into dbo.Co_ItemNameSet values(3,0,'销售费用')
insert into dbo.Co_ItemNameSet values(4,0,'财务费用')
insert into dbo.Co_ItemNameSet values(5,0,'生产成本')
insert into dbo.Co_ItemNameSet values(35,5,'材料')
insert into dbo.Co_ItemNameSet values(36,5,'人工')
insert into dbo.Co_ItemNameSet values(37,5,'制造费用')
insert into dbo.Co_ItemNameSet values(38,35,'原材料')
insert into dbo.Co_ItemNameSet values(39,35,'主要材料')
insert into dbo.Co_ItemNameSet values(40,35,'间辅材料')
insert into dbo.Co_ItemNameSet values(41,36,'工资')
insert into dbo.Co_ItemNameSet values(42,36,'福利')
insert into dbo.Co_ItemNameSet values(43,2,'管理费用子项')
insert into dbo.Co_ItemNameSet values(113,43,'管理费用子项的子项')
查询插入的数据:
--查询数据
select * from Co_ItemNameSet
结果图:
题目需求是:查询ItemId=2及子节点,也就是管理费用和其下属所有节点的信息
操作1:先看看不用CTE递归操作的sql语句如下(需要真是的建两个表进行数据的存放和判断,非常麻烦):
题目需求是:查询ItemId=2及子节点,也就是管理费用和其下属所有节点的信息
操作1:先看看不用CTE递归操作的sql语句如下(需要真是的建两个表进行数据的存放和判断,非常麻烦):
declare @i int
select @i=2;
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
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
insert into #tem([ItemId],[level])
select [ItemId],@level+1
from 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
结果图:
操作2:用CTE递归操作的sql语句如下:
操作2:用CTE递归操作的sql语句如下:
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
结果图:
-----------------------------分析(查看MSDN的分析)----------------------------
主要分析一下用CTE的递归操作:
递归 CTE 由下列三个元素组成:
例程的调用。
递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集,所以它们被称为“定位点成员”。
CTE_query_definitions 被视为定位点成员,除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前,而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。
例程的递归调用。
递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions(就是as里的语句块)。这些查询定义被称为“递归成员”。
终止检查。
终止检查是隐式的;当上一个调用中未返回行时,递归将停止。
递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。
-----------------------------分析(查看MSDN的分析)----------------------------
主要分析一下用CTE的递归操作:
递归 CTE 由下列三个元素组成:
例程的调用。
递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集,所以它们被称为“定位点成员”。
CTE_query_definitions 被视为定位点成员,除非它们引用了 CTE 本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前,而且必须使用 UNION ALL 运算符联接最后一个定位点成员和第一个递归成员。
例程的递归调用。
递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions(就是as里的语句块)。这些查询定义被称为“递归成员”。
终止检查。
终止检查是隐式的;当上一个调用中未返回行时,递归将停止。
递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition --定位点成员
UNION ALL
CTE_query_definition --递归成员.
)
现在让我们看一下递归执行过程:
将 CTE 表达式拆分为定位点成员和递归成员。
运行定位点成员,创建第一个调用或基准结果集 (T0)。
运行递归成员,将 Ti 作为输入,将 Ti+1 作为输出。
重复步骤 3,直到返回空集。
返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。
将 CTE 表达式拆分为定位点成员和递归成员。
运行定位点成员,创建第一个调用或基准结果集 (T0)。
运行递归成员,将 Ti 作为输入,将 Ti+1 作为输出。
重复步骤 3,直到返回空集。
返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。
相关推荐
本文主要介绍了 SQL Server 2005 中公用表表达式(CTE)的递归调用,用于解决树型结构数据的查询问题。CTE 是 SQL Server 2005 中的一种新的查询方式,它可以简化复杂的查询过程。 在本文中,我们使用一个树型结构...
总的来说,SQL Server中的公用表表达式递归功能对于处理层级数据结构非常有用,如组织架构、权限树、产品分类等。通过定义递归CTE,我们可以优雅地解决这些问题,而无需编写复杂的嵌套循环或自连接查询。理解并熟练...
SQL中的公用表表达式(CTE,Common Table Expression)是一种强大的工具,它允许你在执行单个查询时创建临时的结果集,这个结果集可以在后续的查询语句中被引用。CTE通过`WITH AS`短语来定义,它可以提升SQL语句的...
SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,CTE即公用表表达式 公用表表达式(CTE),是一个在查询中定义的临时命名结果集将在from子句中使用它。每个CTE仅被定义一次(但在其作用域内可以被引用任意...
在你可能感兴趣的相关文章中,可以看到更多关于如何在SQL Server中使用递归查询的例子,包括递归子节点、父节点,查询表结构,非循环递归查询,以及处理树形结构的方法等。学习和掌握这些技术,将有助于你更有效地...
公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。 CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。 与派生表的不同之处...
CTE,公用表表达式,是一种在SQL查询中临时定义的虚表,它在查询执行的过程中有效,但不会被持久化存储。CTE的主要用途包括: 1. **递归查询**:通过自我引用,CTE可以实现层次结构数据的遍历,例如在组织结构或者...
在SQL Server中,公用表表达式(Common Table Expression,简称CTE)是一种非常有用的查询构造,它可以临时定义一个结果集,然后在后续的查询中重复使用。CTE的一个强大特性是支持递归,这意味着它可以在自身的基础...
而SQL Server 2005引入的CTE(Common Table Expression,公用表表达式)提供了一种更简洁有效的方式,利用递归查询特性来处理层级数据。 CTE提供了一种可以引用自己的查询结果集的方法,它是一个临时命名的结果集,...
`WITH`语句,也称为公用表表达式(Common Table Expression, CTE),是SQL中的一种临时结果集,用于定义一个临时的逻辑表,这个表只在当前查询中有效。`WITH`语句配合递归选项,可以实现递归查询,非常适合处理树形...
在SQL Server 2005及以上版本,还可以使用公用表表达式(CTE)进行递归查询,以更简洁的方式完成相同的功能。 2. 查找所有父节点(反向递归): 与查找子节点相反,我们从一个节点开始,向上查找其所有父节点直至根...
### SQL中的CTE(公用表表达式)详解 在数据库操作中,经常需要处理复杂的查询逻辑,特别是当涉及到多表连接、嵌套查询时。为了提高SQL查询的可读性和可维护性,引入了CTE(Common Table Expression,公用表表达式...
MySQL 8中的公用表表达式(Common Table Expression,简称CTE)是一种强大的查询工具,它允许你在SQL语句中创建临时的结果集,这个结果集可以在同一语句的其他部分中重复使用。CTE的引入极大地方便了复杂查询的编写...