sqlserver的树形查询
来源:互联网 发布:淘宝让买家改评价 编辑:程序博客网 时间:2024/05/17 00:56
SQL Server没有ORACLE那样专门的树形查询语法,而这却是很多数据库模型中要用到的,比如主机结构,产品分类等。 我查询了很多资料没有一个满意的,基本有3中方案:
1. 简单的带with的递归查询,无法排序,更加无法显示体系架构
2 使用ROW_NUMBER()over功能,比较完美,但某些极端情况不能工作,复杂难懂
3 老外有存储过程来实现,很完美,但建立新的对象很不爽
我综合了各种方法,这是第4中方法,应该很完美了:
WITH tree
AS
(
SELECT ParentAssetID, AssetID,1 AS x2level,nodename,
CAST(nodename AS NVARCHAR(max)) x2name,
CAST(+AssetID AS NVARCHAR(max)) x2id
FROM dbo.Assets
WHERE ParentAssetID IS null
UNION ALL
SELECT c.ParentAssetID, c.AssetID, tree.x2level + 1,c.nodename,
CAST(REPLICATE('-', x2level * 4) + c.nodename AS NVARCHAR(max)) x2name,
tree.x2id +':|:'+ CAST(c.AssetID AS NVARCHAR(max)) x2id
FROM dbo.Assets c
INNER JOIN tree
ON c.ParentAssetID = tree.AssetID
)
SELECT x2name, AssetID, ParentAssetID FROM tree
ORDER BY x2id;
---President
------Vice President
---------CEO
---------CTO
------------Group Project Manager
---------------Project Manager 1
------------------Team Leader 1
---------------------Software Engineer 1
---------------------Software Engineer 2
------------------Test Lead 1
---------------------Tester 1
---------------------Tester 2
---------------Project Manager 2
------------------Team Leader 2
---------------------Software Engineer 3
---------------------Software Engineer 4
------------------Test Lead 2
---------------------Tester 3
---------------------Tester 4
---------------------Tester 5
1. 简单的带with的递归查询,无法排序,更加无法显示体系架构
2 使用ROW_NUMBER()over功能,比较完美,但某些极端情况不能工作,复杂难懂
3 老外有存储过程来实现,很完美,但建立新的对象很不爽
我综合了各种方法,这是第4中方法,应该很完美了:
WITH tree
AS
(
SELECT ParentAssetID, AssetID,1 AS x2level,nodename,
CAST(nodename AS NVARCHAR(max)) x2name,
CAST(+AssetID AS NVARCHAR(max)) x2id
FROM dbo.Assets
WHERE ParentAssetID IS null
UNION ALL
SELECT c.ParentAssetID, c.AssetID, tree.x2level + 1,c.nodename,
CAST(REPLICATE('-', x2level * 4) + c.nodename AS NVARCHAR(max)) x2name,
tree.x2id +':|:'+ CAST(c.AssetID AS NVARCHAR(max)) x2id
FROM dbo.Assets c
INNER JOIN tree
ON c.ParentAssetID = tree.AssetID
)
SELECT x2name, AssetID, ParentAssetID FROM tree
ORDER BY x2id;
---President
------Vice President
---------CEO
---------CTO
------------Group Project Manager
---------------Project Manager 1
------------------Team Leader 1
---------------------Software Engineer 1
---------------------Software Engineer 2
------------------Test Lead 1
---------------------Tester 1
---------------------Tester 2
---------------Project Manager 2
------------------Team Leader 2
---------------------Software Engineer 3
---------------------Software Engineer 4
------------------Test Lead 2
---------------------Tester 3
---------------------Tester 4
---------------------Tester 5
0 0
- sqlserver的树形查询
- SqlServer 递归查询树形数据
- SqlServer 递归查询树形数据
- SqlServer 递归查询树形数据
- sqlserver实现层级树形查询
- SqlServer 递归查询树形数据
- SqlServer 递归查询树形数据
- sqlserver 树形结构查询,单表
- SqlServer找出所有的子节点,并按树形结构、层次查询
- 树形查询的优化
- sqlserver实现树形结构递归查询(无限极分类)
- sqlserver实现树形结构递归查询(无限极分类)
- sqlserver实现树形结构递归查询(无限极分类)
- sqlserver实现层级树形查询(第二弹)
- Sqlserver的版本查询
- Oracle 的树形递归查询
- db2的树形结构查询
- 常用数据库的树形查询
- C++学习记录之STL函数
- android为ImageView使用蒙层
- hibernate环境的搭建
- 第7章 虚拟机类加载机制
- 写一个输入框并将输入的结果在文本框中排序
- sqlserver的树形查询
- Linux 磁阵性能瓶颈定位过程
- Mybatis之配置文件中的objectFactory节点内容说明
- 欢迎使用CSDN-markdown编辑器
- iOS开发中常用的轮子 第四篇 抽屉和侧滑效果
- Spring 漏洞分析
- 无锁编程(五) - RCU(Read-Copy-Update)
- IOS UILabel
- LeetCode(52) Power of Two