CREATE TABLE Hierarchy (Parent VARCHAR(20) NOT NULL, Child VARCHAR(20), CONSTRAINT UIX_ParentChild UNIQUE NONCLUSTERED (Parent,Child))CREATE CLUSTERED INDEX CIX_Parent ON Hierarchy(Parent) GOINSERT Hierarchy VALUES('World','Europe') INSERT Hierarchy VALUES('World','North America') INSERT Hierarchy VALUES('Europe','France') INSERT Hierarchy VALUES('France','Paris') INSERT Hierarchy VALUES('North America','United States') INSERT Hierarchy VALUES('North America','Canada') INSERT Hierarchy VALUES('United States','New York') INSERT Hierarchy VALUES('United States','Washington') INSERT Hierarchy VALUES('New York','New York City') INSERT Hierarchy VALUES('Washington','Redmond') GO此种表示方法不会清楚显示数据中暗含的结构。Parent Child ---------------------------------- ---------------------------------- World Europe World North America Europe France France Paris North America United States North America Canada United States New York United States Washington New York New York City Washington Redmond 而下例则更容易解释:World North America Canada United States Washington Redmond New York New York City Europe France Paris这个是表结构及效果。
CREATE PROCEDURE expand (@current char(20)) AS SET NOCOUNT ON DECLARE @lvl int, @line char(20) CREATE TABLE #stack (item char(20), lvl int) INSERT INTO #stack VALUES (@current, 1) SELECT @lvl = 1 WHILE @lvl > 0 BEGIN IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl) BEGIN SELECT @current = item FROM #stack WHERE lvl = @lvl SELECT @line = space(@lvl - 1) + @current PRINT @line DELETE FROM #stack WHERE lvl = @lvl AND item = @current INSERT #stack SELECT Child, @lvl + 1 FROM Hierarchy WHERE Parent = @current IF @@ROWCOUNT > 0 SELECT @lvl = @lvl + 1 END ELSE SELECT @lvl = @lvl - 1 END -- WHILE这个实现上述效果的储存过程。
(Parent VARCHAR(20) NOT NULL,
Child VARCHAR(20),
CONSTRAINT UIX_ParentChild
UNIQUE NONCLUSTERED (Parent,Child))CREATE CLUSTERED INDEX CIX_Parent
ON Hierarchy(Parent)
GOINSERT Hierarchy VALUES('World','Europe')
INSERT Hierarchy VALUES('World','North America')
INSERT Hierarchy VALUES('Europe','France')
INSERT Hierarchy VALUES('France','Paris')
INSERT Hierarchy VALUES('North America','United States')
INSERT Hierarchy VALUES('North America','Canada')
INSERT Hierarchy VALUES('United States','New York')
INSERT Hierarchy VALUES('United States','Washington')
INSERT Hierarchy VALUES('New York','New York City')
INSERT Hierarchy VALUES('Washington','Redmond')
GO此种表示方法不会清楚显示数据中暗含的结构。Parent Child
---------------------------------- ----------------------------------
World Europe
World North America
Europe France
France Paris
North America United States
North America Canada
United States New York
United States Washington
New York New York City
Washington Redmond 而下例则更容易解释:World
North America
Canada
United States
Washington
Redmond
New York
New York City
Europe
France
Paris这个是表结构及效果。
SET NOCOUNT ON
DECLARE @lvl int, @line char(20)
CREATE TABLE #stack (item char(20), lvl int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @lvl = 1
WHILE @lvl > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN
SELECT @current = item
FROM #stack
WHERE lvl = @lvl
SELECT @line = space(@lvl - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE lvl = @lvl
AND item = @current
INSERT #stack
SELECT Child, @lvl + 1
FROM Hierarchy
WHERE Parent = @current
IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1
END
ELSE
SELECT @lvl = @lvl - 1
END -- WHILE这个实现上述效果的储存过程。
我是用Sqlite 不支持存储过程,对不起这点没讲明.
方法2、如果表结构尚未确定,分区id,pid的模式,改用rootid,order,level的模式