SQL Server BOM展开方法整理
来源:互联网 发布:染色体核型分析软件 编辑:程序博客网 时间:2024/05/22 11:34
---------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-07-06 18:33:50
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258 (转载保留此信息)
-- Subject: SQL Server BOM展开方法整理
----------------------------------------------------------------------------------
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[Name] [nvarchar](10),[PID] [int])
INSERT INTO [tb]
SELECT '1','A','0' UNION ALL
SELECT '2','B','0' UNION ALL
SELECT '3','A1','1' UNION ALL
SELECT '4','B1','2' UNION ALL
SELECT '5','B2','2' UNION ALL
SELECT '6','A11','3' UNION ALL
SELECT '7','A12','3' UNION ALL
SELECT '8','A111','6' UNION ALL
SELECT '9','A112','6' UNION ALL
SELECT '10','A1111','8' UNION ALL
SELECT '11','A1112','8'
--SELECT * FROM [tb]
-->SQL查询如下:
--1.BOM展开并按节点深度排序查询方法:
--1.1 SQL2000 指定某节点展开,并按节点深度排序:
IF OBJECT_ID('dbo.f_GetTree') IS NOT NULL
DROP FUNCTION dbo.f_GetTree;
GO
CREATE FUNCTION dbo.f_GetTree
(
@ID AS INT=NULL
)
RETURNS @r TABLE
(
ID INT,
lvl INT,
px VARBINARY(8000)
)
AS
BEGIN
DECLARE @lvl INT
SET @lvl = 0
IF ISNULL(@ID,0)<>0
INSERT @r VALUES(@ID,@lvl,CAST(@ID AS VARBINARY))
ELSE
INSERT @r
SELECT ID, @lvl ,CAST(ID AS VARBINARY)
FROM tb
WHERE PID = 0
WHILE @@rowcount>0
BEGIN
SET @lvl = @lvl+1;
INSERT @r
SELECT a.ID, @lvl ,b.px+CAST(a.ID AS VARBINARY)
FROM tb a
JOIN @r b
ON a.PID = b.ID
AND b.lvl = @lvl-1
END
RETURN;
END
GO
--显示所有节点:
SELECT a.*, b.lvl
FROM tb a
JOIN dbo.f_GetTree(1) b
ON a.ID = b.ID
ORDER BY b.px
--结果:
/*
ID Name PID lvl
----------- ---------- ----------- -----------
1 A 0 0
3 A1 1 1
6 A11 3 2
8 A111 6 3
10 A1111 8 4
11 A1112 8 4
9 A112 6 3
7 A12 3 2
2 B 0 0
4 B1 2 1
5 B2 2 1
(11 行受影响)
*/
--查询指定节点,如显示节点下的所有节点:
SELECT a.*, b.lvl
FROM tb a
JOIN dbo.f_GetTree(2) b
ON a.ID = b.ID
ORDER BY b.px
/*
ID Name PID lvl
----------- ---------- ----------- -----------
2 B 0 0
4 B1 2 1
5 B2 2 1
(3 行受影响)
*/
--1.2 SQL2005 指定某节点展开,并按节点深度排序:
--显示所有节点:
;WITH t AS
(
SELECT ID,lvl=0,px=CAST(ID AS VARBINARY)
FROM tb t
WHERE PID=0
UNION ALL
SELECT a.ID,lvl+1,CAST(px+CAST(a.ID AS VARBINARY) AS VARBINARY)
FROM tb a
JOIN t b
ON a.PID = b.ID
)
SELECT a.*,lvl
FROM tb a
JOIN t b
ON a.ID=b.ID
ORDER BY b.px
/*
ID Name PID lvl
----------- ---------- ----------- -----------
1 A 0 0
3 A1 1 1
6 A11 3 2
8 A111 6 3
10 A1111 8 4
11 A1112 8 4
9 A112 6 3
7 A12 3 2
2 B 0 0
4 B1 2 1
5 B2 2 1
(11 行受影响)
*/
--显示指定节点,如显示节点下的所有节点:
;WITH t AS
(
SELECT ID,lvl=0,px=CAST(ID AS VARBINARY)
FROM tb t
WHERE ID=2
UNION ALL
SELECT a.ID,lvl+1,CAST(px+CAST(a.ID AS VARBINARY) AS VARBINARY)
FROM tb a
JOIN t b
ON a.PID = b.ID
)
SELECT a.*,lvl
FROM tb a
JOIN t b
ON a.ID=b.ID
ORDER BY b.px
/*
ID Name PID lvl
----------- ---------- ----------- -----------
2 B 0 0
4 B1 2 1
5 B2 2 1
(3 行受影响)
*/
--2. BOM反查并按节点深度排序查询方法:
--2.1 SQL2000 BOM反查,并按节点深度排序:
IF OBJECT_ID('dbo.f_GetPTree') IS NOT NULL
DROP FUNCTION dbo.f_GetPTree;
GO
CREATE FUNCTION dbo.f_GetPTree
(
@ID AS INT=NULL
)
RETURNS @r TABLE
(
ID INT,
PID INT,
lvl INT,
px VARBINARY(8000)
)
AS
BEGIN
DECLARE @lvl INT
SET @lvl = 0
INSERT @r
SELECT ID,PID, @lvl ,CAST(ID AS VARBINARY)
FROM tb
WHERE ID = @ID
WHILE @@rowcount>0
BEGIN
SET @lvl = @lvl+1;
INSERT @r
SELECT a.ID,a.PID, @lvl ,b.px+CAST(a.ID AS VARBINARY)
FROM tb a
JOIN @r b
ON a.ID = b.PID
AND b.lvl = @lvl-1
END
RETURN;
END
GO
--查询指定节点,如反查节点的所有父节点:
SELECT a.*, b.lvl
FROM tb a
JOIN dbo.f_GetPTree(11) b
ON a.ID = b.ID
ORDER BY b.px
/*
ID Name PID lvl
----------- ---------- ----------- -----------
11 A1112 8 0
8 A111 6 1
6 A11 3 2
3 A1 1 3
1 A 0 4
(5 行受影响)
*/
--2.2 SQL2005 BOM反查,并按节点深度排序:
--查询指定节点,如反查节点的所有父节点:
;WITH t AS
(
SELECT ID,PID,lvl=0,px=CAST(ID AS VARBINARY)
FROM tb t
WHERE ID=11
UNION ALL
SELECT a.ID,a.PID,lvl+1,CAST(px+CAST(a.ID AS VARBINARY) AS VARBINARY)
FROM tb a
JOIN t b
ON a.ID = b.PID
)
SELECT a.*,lvl
FROM tb a
JOIN t b
ON a.ID=b.ID
ORDER BY b.px
/*
ID Name PID lvl
----------- ---------- ----------- -----------
11 A1112 8 0
8 A111 6 1
6 A11 3 2
3 A1 1 3
1 A 0 4
(5 行受影响)
*/
- SQL Server BOM展开方法整理
- SQL Server BOM展开方法整理
- SQL Server BOM展开方法整理
- SQL Server BOM展开方法整理
- BOM展开SQL语句
- SQL对BOM多级展开实现
- SQL 与 orcale 数据库对BOM多级展开的实现方法
- 展开BOM
- 展开BOM
- 展开BOM
- 展开BOM
- 展开BOM
- 展开BOM
- 展开BOM
- 展开BOM
- 展开BOM
- 展开BOM
- 展开BOM
- Unity中Screen类
- java -classpath or -cp 的设置和解释
- 第17周项目6-学生成绩统计(2)
- android高仿今日头条 --新闻阅读器
- Spring+SpringMVC+Hibernate整合+入门笔记
- SQL Server BOM展开方法整理
- 漫谈如何学习操作系统原理
- cvCopy与cvCloneImage、cvCopyimage的区别
- Python文件路径具体操作方法经典讲解
- 关于Java常量定义的一点思考
- EE屌丝的数据结构笔记(三)
- android 启动第三方程序的代码
- wikioi 1502 这个月有几天
- 最简单的基于librtmp的示例:接收(RTMP保存为FLV)