首页 / MSSQL / SQL-递归查询在Ora与Mssql

SQL-递归查询在Ora与Mssql

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL-递归查询在Ora与Mssql，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6272字，纯文字阅读大概需要9分钟。

内容图文

test表 ID 地区ID Name 地区名称 Main_ID 地区所属上级ID Sign 地区等级 例如:福建-厦门-湖里 分别是 1，2，3 */ -- 建表 SELECT 1003 ID, ‘福建‘ Name, 0 Main_ID, 1 Sign INTO test union all SELECT 1050 , ‘福州‘ , 1003 , 2 union all SELECT 1051 , ‘厦门‘ , 1003 , 2 union ALL SELECT 1375 , ‘思明‘ , 1051 , 3 union all SELECT 1382 , ‘海沧‘ , 1051 , 3 union all SELECT 1381 , ‘湖里‘ , 1051 , 3 union all SELECT 1374 , ‘集美‘ , 1051 , 3 union all SELECT 1373 , ‘同安‘ , 1051 , 3 union all SELECT 1380 , ‘翔安‘ , 1051 , 3 union ALL SELECT 667582720122 , ‘鼓楼‘ , 1050 , 3 union all SELECT 667582725528 , ‘台江‘ , 1050 , 3 union all SELECT 667582729587 , ‘仓山‘ , 1050 , 3 union all SELECT 667582732602 , ‘马尾‘ , 1050 , 3 union all SELECT 667582735385 , ‘晋安‘ , 1050 , 3 union all SELECT 667582738507 , ‘闽侯‘ , 1050 , 3 union all SELECT 667582742586 , ‘连江‘ , 1050 , 3 union all SELECT 667582745634 , ‘罗源‘ , 1050 , 3 union all SELECT 667582748358 , ‘闽清‘ , 1050 , 3 union all SELECT 667582751824 , ‘永泰‘ , 1050 , 3 union all SELECT 667582755215 , ‘平潭‘ , 1050 , 3 union all SELECT 667582760309 , ‘福清‘ , 1050 , 3 union all SELECT 667582764565 , ‘长乐‘ , 1050 , 3;

      这里才发现，在 Mssql 中，可以通过这样创建表，有点类似 Oracle 语法中的 Create Table as Select 的方式。通过导出 DDL 可以看到表 test 中列的精度。  

CREATE TABLE [dbo].[test](
    [ID] [numeric](12, 0) NOT NULL,
    [Name] [varchar](4) NOT NULL,
    [Main_ID] [int] NOT NULL,
    [Sign] [int] NOT NULL
) ON [PRIMARY]
GO

发现 number 类型和 varchar 类型，都是刚好是 id 列和 name 列的最大精度。表环境拟好以后，可以执行 SQL。  

WITH CTE AS 
(
--父项
SELECT  a.*,1 level 
FROM test a WHERE ID=1003
UNION ALL 
--递归结果集中的下级 
SELECT a.* , level + 1 as level
FROM test a
INNER JOIN CTE b ON b.ID=a.Main_ID
)select * from CTE;

--结果集
ID                                      Name Main_ID     Sign        level
--------------------------------------- ---- ----------- ----------- -----------
1003                                    福建   0           1           1
1050                                    福州   1003        2           2
1051                                    厦门   1003        2           2
1375                                    思明   1051        3           3
1382                                    海沧   1051        3           3
1381                                    湖里   1051        3           3
1374                                    集美   1051        3           3
1373                                    同安   1051        3           3
1380                                    翔安   1051        3           3
667582720122                            鼓楼   1050        3           3
667582725528                            台江   1050        3           3
667582729587                            仓山   1050        3           3
667582732602                            马尾   1050        3           3
667582735385                            晋安   1050        3           3
667582738507                            闽侯   1050        3           3
667582742586                            连江   1050        3           3
667582745634                            罗源   1050        3           3
667582748358                            闽清   1050        3           3
667582751824                            永泰   1050        3           3
667582755215                            平潭   1050        3           3
667582760309                            福清   1050        3           3
667582764565                            长乐   1050        3           3

　　其实，从这个结果集就很好理解递归查询应用场景，以地点这个场景来解释层次查询是非常适合的，如果表中没有 Sign 这个列，在 Sql 中的 level 也能非常好的分层。第一层就是福建，福建下面的第二层地点就是福州和厦门， Sign = 3 的就是第三层地名。把它们关联起来的是Id 与 Main_id ，一般我们把 Main_id 叫 Parent id ，即它的父节点 id 。比如地点马尾，它的 Main_id 是1050，而 id 是1050的就是马尾的父节点——福州。如果我们把整个结果集当成一棵树的结构的话，在以上 Sql 中，是从 id = 1003 开始，遍历整棵树，Sign = 1 是根的话，Sign = 3 的就是这棵树的叶，这样的查询就是自顶向下(树形结构是倒着的)的一种层次查询。(仅个人理解，可能不准确)
　　而下面这样，就是自底向上的查询了，通过一个叶子节点去查找父节点。也很好理解，第一部分先获取到叶子节点的 id，第二部分，通过已经查到的叶子节点数据(cte as b)的父节点，来找父节点的数据(b.Main_id = test.id)。　　

with cte as (
select a.*,1 as level from test a where id = 667582751824
union all
select a.*,level + 1 as level from test a inner join cte as b
 on a.id = b.Main_ID
) select * from cte;

--结果集
ID                                      Name Main_ID     Sign        level
--------------------------------------- ---- ----------- ----------- -----------
667582751824                            永泰   1050        3           1
1050                                    福州   1003        2           2
1003                                    福建   0           1           3

       在 Oracle 语法中，层次查询的语法与 Mssql 中不一样，以前在用到的时候，一直难以理解是怎么个关联关系的，直到在一个博客中看到一种说法：prior 就是"找"的意思，prior id 就是找叶子节点，自顶而下查询；prior parent_id 就是找父节点，即自底向上查询。这样解释起来就非常有意思而且便于记忆，是个非常棒的点。以下是 Oracle / DM 对应的SQL。  

select * from test start with id=1003 connect by prior id= main_id;
select a.*,level from test a start with id = 667582735385 connect by prior main_id = id;

 

SQL-递归查询在Ora与Mssql

标签：部分   工作   记忆   acl   strong   类型   har   tab   移植   

本文系统来源：https://www.cnblogs.com/DimInk/p/10716469.html

内容总结

以上是互联网集市为您收集整理的SQL-递归查询在Ora与Mssql全部内容，希望文章能够帮你解决SQL-递归查询在Ora与Mssql所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/506148.html

来源：【匿名】