SQLServer CTE递归和循环对比的优势--典型案例_Wikey_Zhang的博客

相关文章推荐
憨厚的小马驹 · java ...· 1 月前 ·
喝醉的卤蛋 · SQL Server ...· 1 月前 ·
个性的灯泡 · 树形结构数据库表设计与django结合 - ...· 1 月前 ·
活泼的水煮肉 · Mysql 递归查询所有父节点 - 刘小吉 · 1 月前 ·
奔跑的创口贴 · mysql ...· 1 月前 ·
高大的海龟 · 无法杀死正在运行的进程· 9 月前 ·
气宇轩昂的硬盘 · JQuery的 ...· 1 年前 ·
逼格高的碗 · C# 之判断一个字符是否是汉字 - ...· 1 年前 ·
性感的小虾米 · Example: Deploying ...· 1 年前 ·
神勇威武的消炎药 · python ...· 1 年前 ·
首先，我们新建一张测试用的临时表#country，其中包含三个字段，AreaNam（地名），BelongTo（上级地名），Msg（地方简介）
Create table #country (AreaNam NVARCHAR(10),BelongTo Nvarchar(10),Msg varchar(100))
下面我们往这张表里插入一堆测试数据： 
INSERT INTO #country
SELECT '中国','中国',null union all
SELECT '江苏','中国',null union all
SELECT '南京','江苏',null union all
SELECT '无锡','江苏',null union all
SELECT '徐州','江苏',null union all
SELECT '扬州','江苏',null union all
SELECT '苏州','江苏',null union all
SELECT '六合区','南京',null union all
SELECT '江宁区','南京',null union all
SELECT '浦口区','南京',null union all
SELECT '仙林区','南京',null union all
SELECT '建邺区','南京',null union all
SELECT '宝应','扬州',null union all
SELECT '仪征','扬州',null union all
SELECT '小官庄','宝应',null union all
SELECT '范水','宝应',null union all
SELECT '鲁垛','宝应',null union all
SELECT '安宜','宝应',null union all
SELECT '组全','小官庄',null union all
SELECT '房桥','小官庄',null union all
SELECT '直下沟','小官庄',null union all
SELECT '山东','中国',null union all
SELECT '济南','山东',null union all
SELECT '青岛','山东',null union all
SELECT '淄博','山东',null union all
SELECT '烟台','山东',null union all
SELECT '张店','淄博',null union all
SELECT '博山','淄博',null union all
SELECT '淄川','淄博',null union all
SELECT '龙王山','浦口区',null union all
SELECT '高新区','浦口区',null union all
SELECT '陆军指挥学院','浦口区',null union all
SELECT '南京信息工程大学','浦口区',null union all
SELECT '金陵学院','浦口区',null 
到这里，表结构已经完成了，这张表的表结构应该不难理解。 
但是下面问题来了，有要求查找出南京包含南京以内（属于南京）的所有地名，因为如果数据量比较大的话，我们根本无法确定一个城市往下分了多少级地名，如果分的层级太多的话，使用循环取实现查询结果也是一个可行的方案，sql如下： 
DECLARE @CITY NVARCHAR(MAX)='南京'
Create table #TEMP (AreaNam NVARCHAR(10),BelongTo Nvarchar(10),Msg varchar(100))
Create table #tempAreaname (AreaNam NVARCHAR(10))
Create table #tempAreanametemp (AreaNam NVARCHAR(10))
INSERT INTO #tempAreaname 
SELECT @CITY
INSERT INTO #TEMP
SELECT * FROM #country WHERE AreaNam=@CITY
WHILE 1=1
BEGIN
	insert into #tempAreanametemp
	SELECT areanam  FROM #country WHERE belongto IN(select areanam from #tempAreaname)
	IF @@ROWCOUNT<>0
	BEGIN 
		INSERT INTO #TEMP
		SELECT * FROM #country WHERE belongto IN(select areanam from #tempAreaname)
		delete from #tempAreaname
		insert into #tempAreaname
		select * from #tempAreanametemp
		delete from #tempAreanametemp		
	BEGIN	
		SELECT * FROM #TEMP
		DROP TABLE #TEMP
		DROP TABLE #tempAreaname
		DROP TABLE #tempAreanametemp
		RETURN
查询结果如下： 
AreaNam BelongTo Msg
 南京 江苏 NULL
 六合区 南京 NULL
 江宁区 南京 NULL
 浦口区 南京 NULL
 仙林区 南京 NULL
 建邺区 南京 NULL
 龙王山 浦口区 NULL
 高新区 浦口区 NULL
 陆军指挥学院 浦口区 NULL
 南京信息工程大学 浦口区 NULL
 金陵学院 浦口区 NULL 
这正是我们所想要的结果，但是总感觉这写法太复杂而且执行效率也不是很高，当然啦用循环去写的话肯定也有简单一点的写法的，这不是我们今天的重点。今天的重点是用CTE递归的方式去实现我们所想要的结果,SQL如下： 
WITH CTE AS (
SELECT AreaNam,BelongTo,Msg FROM #country WHERE AreaNam='南京'
UNION ALL
SELECT  A.AreaNam,A.BelongTo,A.Msg FROM #country A INNER JOIN CTE B ON A.BelongTo=B.AreaNam
SELECT * FROM CTE 
查询结果也是一样一样的： 
AreaNam BelongTo Msg
南京 江苏 NULL
六合区 南京 NULL
江宁区 南京 NULL
浦口区 南京 NULL
仙林区 南京 NULL
建邺区 南京 NULL
龙王山 浦口区 NULL
高新区 浦口区 NULL
陆军指挥学院 浦口区 NULL
南京信息工程大学 浦口区 NULL
金陵学院 浦口区 NULL
                    首先，我们新建一张测试用的临时表#country，其中包含三个字段，AreaNam（地名） ，BelongTo（上级地名） ，Msg（地方简介）Create table #country (AreaNam NVARCHAR(10),BelongTo Nvarchar(10),Msg varchar(100))下面我们往这张表里插入一堆测试数据：INSERT INTO #countryS
				一、问题背景
某日，开发同事上报一sql性能问题，一条查询好似一直跑不出结果，查询了n小时，还未返回结果。比较诡异的是同样的sql，相同的数据量，相同的表大小，且在服务器硬件配置相同的情况下，在另外一套环境查询非常快，毫秒级。
第一时间排查了异常环境的查询进程stack，并抓取了一分钟的strace。从结果得知进程是正常执行的，那么看起来就是查询慢的问题了。
最终发现是递归查询出现了死循环，以下内...
drop table ##T1
;with x1 as (
	select row_number()over(order by id) ids,id,'select @t='+replace(replace(replace(replace(cc,'ml',''),'g',''),'x','*'),'×','*') sql from (
		select id,title,replace(bb,substring(ctool.dbo.Ge
				在一般的系统开发中，我们经常遇到一类问题：查询出某条记录以及与该条记录相关的其他记录。例如，现在需要查询出西湖区以及西湖区所属的市和省，这时候就需要用到SQL递归查询。我在这里构造了一张数据表[tb_Test]，其数据如下所示：
Id	 Name	ParentId
1	浙江省	  NULL
2	杭州市	   1
3	湖州市	   1
4	滨江区	   2
5	拱墅区	   2
6	西湖区	   2
7	吴兴区	   3
8	南浔区	   3
9	长兴县	   3
向下递归
假设我们现在需要查询杭州市及其下属的区县，其代码如下所示：
with cte as
   select Id,Nam
				代码如下:–由父项递归下级 with cte(id,parentid,text) as (–父项 select id,parentid,text from treeview where parentid = 450 union all –递归结果集中的下级 select t.id,t.parentid,t.text from treeview as t inner join cte as c on t.parentid = c.id ) select id,parentid,text from cte ——————— –由子级递归父项 with cte(id,parentid,text) as
				公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
递归查询通常用于返回分层数据（即遍历树结构）
 递归 CTE 的结构(MSDN)
递归 CTE 由下列三个元素组成：
例程的调用。 
递归 CTE 的第一个调用包括一个或多个由 UNION ALL、U
				设置重复执行 SQL 语句或语句块的条件。 只要指定的条件为真，就重复执行语句。 可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。
-- Syntax for SQL Server and Azure SQL Database 
WHILE Boolean_expression 
  { sql_statement | statement_block | BREAK | CONTINUE }
Boolean_expressi...
				SQL SERVER while循环
在SQL数据库中，可以通过WHILE实现循环，下面就将为您介绍SQL循环执行while控制，希望对您提升WHILE的使用水平能够有些帮助。
WHILE Boolean_expression
 { sql_statement | statement_block }
 [ BREAK ]
 { sql_statement | st...
;with x1 as (
	select '202002' dates,'-0.1532' value union all  --2020年2-6月环比
	select '202003','0.2417' union all
	select '202004','0.1325' union all
				CTE（Common Table Expression，通用表达式）是 SQL Server 中的一种临时结果集，可以像视图一样使用。递归查询是一种特殊的查询方式，可以处理具有层次结构的数据。CTE 递归查询可以通过 WITH 子句实现。
下面是一个例子，查询某个员工的所有下属：
WITH EmployeeHierarchy AS
    SELECT EmployeeID, SupervisorID, FirstName, LastName, 1 AS Level
    FROM Employees
    WHERE EmployeeID = @EmployeeID
    UNION ALL
    SELECT e.EmployeeID, e.SupervisorID, e.FirstName, e.LastName, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.SupervisorID = eh.EmployeeID
SELECT EmployeeID, SupervisorID, FirstName, LastName, Level
FROM EmployeeHierarchy
ORDER BY Level, LastName, FirstName
这个查询中，EmployeeHierarchy 是一个递归 CTE，它的初始查询返回指定员工的基本信息和层级（Level）为 1。然后，递归查询连续地将下属的信息加入到结果集中，直到没有下属为止。
注意，递归查询必须包含一个初始查询和一个递归查询，它们之间使用 UNION ALL 连接。初始查询返回基本信息，递归查询使用先前的结果集来获得更多信息。递归查询必须引用 CTE 的名称，以便递归调用它本身。在递归查询中，必须有一个递归终止条件，否则查询将进入无限循环。在上面的例子中，递归终止条件是没有下属。
最后，查询结果按照层级、姓氏和名字排序，以显示员工的层次结构。