Tree (ID [Integer]
INSERT INTO Tree (ID
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
T(F
INSERT INTO T (F
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
UNION ALL
SELECT
參考 Tree 表中的父子關系
ID Counts
答案及簡單分析:
/*
看了前幾個人的答案
但是 T
icevi(按鈕工廠) 的建議是非常值得提倡的
但是若用其提供表現形式
許多高效的樹型結構論壇也確實是存儲並維護各個節點的層次信息的數據
顯示起來僅需一條 SQL 即可!
下面是我的參考答案
應最好主動維護的
方法一: UDF 遞歸實現! 有
*/
alter FUNCTION dbo
(@X integer)
RETURNS VARCHAR(
AS
BEGIN
DECLARE @ID integer
DECLARE @ReturnValue VARCHAR(
SELECT TOP
FROM tree
WHERE [id] = @X
IF @ID <> @X
BEGIN
SELECT @ReturnValue = cast(ISNULL(dbo
END
ELSE SET @ReturnValue = @ID
RETURN @ReturnValue
END
go
/*
方法二: 無任何限制
改進了一下:
*/
alter function GetAllAncestors (@X integer)
returns varchar(
as
begin
declare @ReturnValue varchar(
declare @ID integer
declare @ParentID integer
set @ID =
select top
from tree
where ID = @X
while @id <> @parentid and @parentid <>
and
begin
if @ReturnValue is not null
set @ReturnValue =
set @ReturnValue= cast(@id as varchar) + isnull(@ReturnValue
set @id =
select top
from tree
where ID = @parentid
end
set @ReturnValue =
if @id>
set @ReturnValue = cast(@id as varchar) + isnull(@ReturnValue
if @parentid =
set @ReturnValue =
return(@ReturnValue)
end
go
/*
方法一是
方法二是思路簡單清晰
若是本題分為兩問:
可能大家就會受到一些啟發!
函數定義完
*/
select id
from T
where
from tree
select id
from T
where
from tree
/*
另外還要說一下封裝的程度的問題
本題就不適合定義函數直接得到最終結果!
以上答案僅供參考!!
歡迎繼續參與討論!
*/
From:http://tw.wingwit.com/Article/os/youhua/201311/10783.html