﻿ 簡單查詢樹形結構數據庫表_電腦知識網

# 簡單查詢樹形結構數據庫表

2022-06-13   來源: Oracle

在與同事討論中得到一個思路不用嵌套調用直接用一個語句就可以生成樹實事上他已經做好了該程序他用了臨時表寫了一個存儲過程我改寫為一個表值函數供大家參考

表結構及表值函數如下

查詢樹表語句

/**//**** Object:  Table [dbo][Tree]    Script Date: :: ***/
CREATE TABLE [dbo][Tree] (
[ID] [int] IDENTITY ( ) NOT NULL
[PID] [int] NULL
[Name] [varchar] () COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE  CLUSTERED  INDEX [IX_Tree] ON [dbo][Tree]([PID]) ON [PRIMARY]
GO

ALTER TABLE [dbo][Tree] WITH NOCHECK ADD
CONSTRAINT [PK_Tree] PRIMARY KEY  NONCLUSTERED
(
[ID]
)  ON [PRIMARY]
CONSTRAINT [子ID不能等於父ID] CHECK ([ID] <> [PID])
GO

CONSTRAINT [FK_Tree_Tree] FOREIGN KEY
(
[PID]
) REFERENCES [dbo][Tree] (
[ID]
)
GO

/**//****** 對象:  用戶定義的函數 dbofGetTreeTable

CREATE FUNCTION dbofGetTreeTable
(
@ID int= null
)
RETURNS @Tab TABLE(ID int PID int Name varchar() Lev int)
AS
BEGIN
Declare @lev int
Set @lev=

While @lev= or @@ROWCount>
Begin
Set @Lev=@Lev+
Insert @Tab(ID PID Name Lev)
Select ID PID Name @Lev From Tree Where (@Lev= and ((PID=@ID) or
(@ID is null and PID is null))) or (PID in (Select ID From @Tab Where Lev=@Lev))
order by ID
End
RETURN
END

GO

Insert Tree(PID Name) values(null 公司)
Insert Tree(PID Name) values( IT)
Insert Tree(PID Name) values( Fin)
Insert Tree(PID Name) values( XZ)
Insert Tree(PID Name) values( HR)
GO

直接查詢Select * from dbofGetTreeTable（null）就可以輸入所有記錄

From:http://tw.wingwit.com/Article/program/Oracle/201311/17837.html