还是树型结构的查询问题。
ID PID AName
1 NULL A
2 1 B
3 1 C
4 2 D
5 4 E
如何查询得到：
ID NName
1 A
2 A-B
3 A-C
4 A-B-D
5 A-B-D-E
[解决办法]
--建立測試環境
Create Table TEST
(ID Int,
PID Int,
AName Varchar(10))
Insert TEST Select 1, NULL, 'A '
Union All Select 2, 1, 'B '
Union All Select 3, 1, 'C '
Union All Select 4, 2, 'D '
Union All Select 5, 4, 'E '
GO
--建立函數
Create Function GetPath(@ID Int)
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = Rtrim(AName), @ID = PID From TEST Where ID = @ID
While @@ROWCOUNT > 0
Select @ID = PID, @S = Rtrim(AName) + '- ' + @S From TEST Where ID = @ID
Return @S
End
GO
--測試
Select
ID,
dbo.GetPath(ID) As NName
From
TEST
GO
--刪除測試環境
Drop Table TEST
Drop Function GetPath
--結果
/*
IDNName
1A
2A-B
3A-C
4A-B-D
5A-B-D-E
*/
