id,scid,name
ID是自动编号.
如果是首层的话,scid为0.不确定会分出多少层的节点.
要求列出所有的子层包括子层旗下的子层
如:广东省,id=1,scid=0
湖南省,id=2,scid=0
广州市,id=3,scid=1
长沙市,id=4,scid=2
深圳市,id=5,scid=1列出结果为:
广东省
广州市
深圳市
湖南省
长沙市
ID是自动编号.
如果是首层的话,scid为0.不确定会分出多少层的节点.
要求列出所有的子层包括子层旗下的子层
如:广东省,id=1,scid=0
湖南省,id=2,scid=0
广州市,id=3,scid=1
长沙市,id=4,scid=2
深圳市,id=5,scid=1列出结果为:
广东省
广州市
深圳市
湖南省
长沙市
GO-- 建立演示环境
CREATE TABLE Dept(
id int PRIMARY KEY,
parent_id int,
name nvarchar(20))
INSERT Dept
SELECT 0, 0, N'<全部>' UNION ALL
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO-- 查询指定部门下面的所有部门
DECLARE @Dept_name nvarchar(20)
SET @Dept_name = N'MIS'
;WITH
DEPTS AS(
-- 定位点成员
SELECT * FROM Dept
WHERE name = @Dept_name
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.*
FROM Dept A, DEPTS B
WHERE A.parent_id = B.id
)
SELECT * FROM DEPTS
GO-- 删除演示环境
DROP TABLE Dept
/*id parent_id name
----------- ----------- --------------------
6 4 MIS
7 6 UI
8 6 软件开发
9 8 内部开发(4 行受影响)*/
-->Title:Generating test data
-->Author:wufeng4552
-->Date :2009-09-30 08:52:38
set nocount on
if object_id('tb','U')is not null drop table tb
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
-->Title:查找指定節點下的子結點
if object_id('Uf_GetChildID')is not null drop function Uf_GetChildID
go
create function Uf_GetChildID(@ParentID int)
returns @t table(ID int)
as
begin
insert @t select ID from tb where ParentID=@ParentID
while @@rowcount<>0
begin
insert @t select a.ID from tb a inner join @t b
on a.ParentID=b.id and
not exists(select 1 from @t where id=a.id)
end
return
end
go
select * from dbo.Uf_GetChildID(5)
/*
ID
-----------
6
7
*/
-->Title:查找指定節點的所有父結點
if object_id('Uf_GetParentID')is not null drop function Uf_GetParentID
go
create function Uf_GetParentID(@ID int)
returns @t table(ParentID int)
as
begin
insert @t select ParentID from tb where ID=@ID
while @@rowcount!=0
begin
insert @t select a.ParentID from tb a inner join @t b
on a.id=b.ParentID and
not exists(select 1 from @t where ParentID=a.ParentID)
end
return
end
go
select * from dbo.Uf_GetParentID(2)
/*
ParentID
-----------
1
0
*/
select id,scid,name
,case when not scid=0 then
scid else (select id from table p1 where p1.id=p.id) end tid
,(select name from table where id=p.scid) as scname
from table p
order by tid,id