--> 生成測驗資料表: [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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/65386.html
標籤：MySQL
上一篇：hbase與rdbms的區別
下一篇：mysql禁用system