[转帖]sqlserver-用 WITH 和 UNION(或者UNION ALL)实现递归查询

freeflydom

2023年5月29日 10:44 本文热度 499

WITH

WITH提供了一种撰写用于更复杂查询辅助语句的方法。这些通常被称为公用数据表表示式或CTE（Common Table Expressions）的宣告可以被想成是定义仅存在于一个查询中的临时数据表。WITH子句中的每个辅助语句都可以是select、insert、update或delete；并且WITH子句本身附加到select、insert、update或delete的主语句。

select in WITH

select中WITH的基本价值是将复杂的查询分解为较为简单的部分。例子如下：

WITH regional_sales AS（
select region,SUM（amount）AS total_sales
from orders
GROUP BY region
）,top_regions AS（
select region
from regional_sales
where total_sales >（select SUM（total_sales）/10 from regional_sales）
）
select region,
product,
SUM（quantity）AS product_units,
SUM（amount）AS product_sales
from orders
where region IN（select region from top_regions）
GROUP BY region,product;

其中仅显示最上层销售区域中的每个产品的销售总计。WITH子句定义了两个名为regional_sales和top_regions的辅助语句，其中top_size使用region_sales的输出，top_regions的输出在主select语句中使用。这个例子本来可以不用WITH编写，但是我们需要两层的select子查询。按照这种方式更容易一些。

递归循序

一个非常简单的例子是这个查询来从1到100的整数求和：

WITH t3 AS 
(
select 0 as val
union ALL
select val+1 as val 
from t3 
where  val <100 
)
select sum(val) as sum from t3

结果：

递归查询通常用于处理分层或树状结构的数据。

一个实用的例子是，一种多层级结构的部门结构，要查询某部门与该部门底下的所有子部门，和子部门下的子部门的部门ID和部门名称。

一张简单的部门表如下：

有两种情况：

1.有卡层次,例如卡最多三层，部门1->部门1.1->部门1.1.1

这种情形的可以选择left jion的方式:

2.没有卡层，在创建部门的时候不进行层级的卡控，这个时候是没有办法选择用left join来实现，可以考虑用递归的方式，我们这时可使用WITH：

这样就优美的完成了递归循环！！

总结：前两周第一次看到这种写法的时候，确实被惊艳到了！觉得很酷！不过一直都没有在实际中用到，在前几天刚好碰到有个不卡层级的部门结构需要查询部门与所有子部门的一些资料，脑袋就突然想到这个写法！最后完美的实现功能，所以想开通第一篇博客来记录下来！

参考：

遞迴查詢（Common Table Expressions）

————————————————

原文链接：https://blog.csdn.net/Ryomaku/article/details/100068078

该文章在 2023/5/29 10:44:17 编辑过

关键字查询

实现

正在查询...

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。