用SQl编写树形菜单

[木可大大]

用SQl编写树形菜单

[htl258_Tony]

[Quote=引用楼主 wy58555264 的回复:]
用SQl编写树形菜单
[/Quote]不知道你想怎么编

[tiantian789456]

ding xia

[htl258_Tony]

--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'

--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
    SET @Level=@Level+1
    INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
    FROM @t a,@t_Level b
    WHERE a.PID=b.ID
        AND b.Level=@Level-1
END

--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/

没有数据自己参考

[--小F--]

CREATE TABLE BOM(PID INT,ID INT)
INSERT INTO BOM SELECT 801,101
INSERT INTO BOM SELECT 801,102
INSERT INTO BOM SELECT 801,103
INSERT INTO BOM SELECT 801,601
INSERT INTO BOM SELECT 601,101
INSERT INTO BOM SELECT 601,105
INSERT INTO BOM SELECT 601,501
INSERT INTO BOM SELECT 501,106
INSERT INTO BOM SELECT 501,121
GO

CREATE FUNCTION F_GETROOT(@PID INT)
RETURNS INT
AS
BEGIN
    DECLARE @ID INT
    WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID)
    BEGIN
        SET @ID=@PID
        SELECT @PID=PID FROM BOM WHERE ID=@ID
    END
    RETURN @PID
END
GO

SELECT PID=DBO.F_GETROOT(PID),ID FROM BOM
GO

/*
PID         ID
----------- ----------- 
801         101
801         102
801         103
801         601
801         101
801         105
801         501
801         106
801         121
*/


DROP FUNCTION F_GETROOT
DROP TABLE BOM
GO




--生成测试数据
create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute  varchar(20))
insert into BOM_1 select 1 ,'A' ,'A1',1,'采购'
insert into BOM_1 select 2 ,'A' ,'A2',2,'生产'
insert into BOM_1 select 3 ,'A2','A3',3,'生产'
insert into BOM_1 select 4 ,'A2','A4',2,'采购'
insert into BOM_1 select 5 ,'A3','A5',2,'采购'
insert into BOM_1 select 6 ,'A3','A6',1,'采购'
insert into BOM_1 select 7 ,'B' ,'B1',1,'采购'
insert into BOM_1 select 8 ,'B' ,'B2',2,'生产'
insert into BOM_1 select 9 ,'B2','B3',3,'生产'
insert into BOM_1 select 10,'B2','B4',2,'采购'
insert into BOM_1 select 11,'B3','B5',2,'采购'
insert into BOM_1 select 12,'B3','B6',2,'采购'
go
   

--创建用户定义函数，用于取每个父节点下子节点的采购配置信息
create function f_stock(@bom_head varchar(20))
returns @t table(bom varchar(20),number int)
as
begin 
    declare @level int
    declare @a table(bom varchar(20),number int,products_attribute varchar(20),[level] int)
    set @level=1

    if exists(select 1 from BOM_1 where bom_head=@bom_head)    
    insert into @a 
    select bom_child,number,products_attribute,@level 
    from BOM_1 
    where bom_head=@bom_head
    
    while exists(select 1 from @a where [level]=@level and products_attribute='生产')
    begin
        set @level=@level+1
        insert into @a(bom,number,products_attribute,[level])
        select a.bom_child,a.number,a.products_attribute,@level 
        from BOM_1 a,@a b
        where a.bom_head=b.bom and b.[level]=@level-1
    end
    
    insert into @t(bom,number) select bom,number from @a where products_attribute='采购'
    return
end
go


--执行调用，取父节点'A'一个标准配置分解的采购信息及数量
select * from dbo.f_stock('A')






--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go

--创建用户定义函数用于取每个父节点下子节点的采购配置信息
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
    declare @i int
    set @i = 1
    insert into @t select ID,PID,@i from BOM where PID = @ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.PID,@i 
        from 
            BOM a,@t b 
        where 
            a.PID=b.ID and b.Level = @i-1
    end
    return
end
go

--执行查询
select ID from dbo.f_getChild(3)
go

--输出结果
/*
ID
----
5
6
7
*/

--删除测试数据
drop function f_getChild
drop table BOM





创建用户定义函数,每个子节点de父节点的信息


--生成测试数据
create table BOM(ID int,parentID int,sClassName varchar(10))
insert into BOM values(1,0,'1111'      )
insert into BOM values(2,1,'1111_1'    )
insert into BOM values(3,2,'1111-1-1'  )
insert into BOM values(4,3,'1111-1-1-1') 
insert into BOM values(5,1,'1111-2'    )

go

--创建用户定义函数,每个子节点de父节点的信息
create function f_getParent(@ID int)
returns varchar(40)
as
begin
    declare @ret varchar(40)

    while exists(select 1 from BOM where ID=@ID and parentID<>0)
    begin
        select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'')
        from
            BOM a,BOM b
        where
            a.ID=@ID and b.ID=a.parentID
    end
    
    set @ret=stuff(@ret,1,1,'')
    return @ret
end
go

--执行查询
select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOM
go

--输出结果
/*
ID          parentID                                 
----------- ---------------------------------------- 
1           
2           1
3           1,2
4           1,2,3
5           1   
*/

--删除测试数据
drop function f_getParent
drop table BOM
go

[zimu312500]

如果是那种层次结构的话，可以用游标进行递归吧

[木可大大]

用游标进行递归，该怎么写呢？

[htl258_Tony]

[Quote=引用 6 楼 wy58555264 的回复:]
用游标进行递归，该怎么写呢？
[/Quote]
循环和游标的方式差不多，参考3楼，以树形结构显示。

[nalnait]

[Quote=引用 6 楼 wy58555264 的回复:]
用游标进行递归，该怎么写呢？
[/Quote]
为什么要用游标？

[Ray_Zhang]

原理就是递归

[xdy3008]

----------------------------案例分析------------------------------------
树形菜单按层迭代：

if object_id('region') is not null drop table [tb]
create table region([id] int,[col1] varchar(50),[col2] int)
insert region
select 0,    'China',    NULL     union all
select 1,    'HuBei Province',    0 union all
select 2,    'WuHan City',    1 union all
select 3,    'HuangGang City',    1 union all
select 4,    'HuangShi City',    1 union all
select 5,    'Wuchang Area',    2 union all
select 6,    'Macheng Area',    3 union all
select 7,    'XinZhou Area',    2 union all
select 8,    'HuangShi',    4 union all
select 9,    'HeNan Province',    0 union all
select 10,    'XinXiang City',    9 union all
select 11,    'aaa',    10 union all
select 12,    'bbb',    10 

-- 以下查询所有记录，但是没有一层层的迭代到底
with tree(id,col1,col2,treeLevel) as(
    select *, 0 from region where id = 0
    union all
    select a.*, treeLevel+1 from region a,tree where a.col2 =tree.id
)
select * from tree order by treeLevel, col2

	0	China	NULL	0
	1	HuBei Province	0	1
	9	HeNan Province	0	1
	2	WuHan City	1	2
	3	HuangGang City	1	2
	4	HuangShi City	1	2
	10	XinXiang City	9	2
	5	Wuchang Area	2	3
	7	XinZhou Area	2	3
	6	Macheng Area	3	3
	8	HuangShi	4	3
	11	aaa	10	3
	12	bbb	10	3

--深度排序显示处理   
--生成每个节点的编码累计(相同当单编号法的编码)  
drop table #level_string
create table #level_string(level char(20), string char(50)) 
insert into #level_string
select '0','' union all
select '1','|____' union all
select '2','    |____' union all
select '3','        |____'
 
DECLARE  @t_Level  table(ID char(3), col1 char(50), col2 char(50), level int, Sort varchar(8000))   
DECLARE  @Level   int   
SET   @Level=0   
  insert @t_Level select ID, region.col1, region.col2, '', '/' + cast(id  as varchar(200)) + '/' as sort
	FROM   region   
  WHERE   col2  is null
WHILE   @@ROWCOUNT>0    
begin
SET   @Level=@Level+1   
  insert  @t_Level   select a.ID, a.col1, a.col2, @Level, rtrim(b.Sort) + rtrim(a.ID)+'/' as Sort    
	  FROM   region a, @t_Level   b   
	  WHERE  b.id =  a.col2  
	  and b.level = @Level-1  
  order by col1 
END   

--select * from @t_Level order by sort 
--显示结果   
SELECT   a.sort,  b.string, a.col1,  (rtrim(b.string) + a.col1)
	FROM     @t_Level a, #level_string b  
where a.level = b.level
order by sort 

----------------------------案例分析------------------------------------
分割字符串成记录
if object_id('#tt') is null
create table #tt (a int)
declare @s varchar(100),@sql varchar(8000)
set @s='1,2,3,4,5'
select @sql='insert #tt select '+ REPLACE(@s,',' ,' union all select ')
print @sql
exec (@sql)
select * from #tt


China               China                                             
HuBei Province      |____HuBei Province                                    
WuHan City              |____WuHan City                                        
Wuchang Area                |____Wuchang Area                                      
XinZhou Area                |____XinZhou Area                                      
HuangGang City          |____HuangGang City                                    
Macheng Area                |____Macheng Area                                      
HuangShi City           |____HuangShi City                                     
HuangShi                    |____HuangShi                                          
HeNan Province      |____HeNan Province                                    
XinXiang City           |____XinXiang City                                     
aaa                         |____aaa                                               
bbb                         |____bbb     

[xdy3008]

或者如下方法：
select * from Dept;
ID Parent_ID DeptName
0 NULL All
1 0 Financle Dept
2 0 Admin Dept
3 0 Business Dept
4 0 Serveice Dept
5 4 Sale Dept
6 4 MIS
7 6 UI
8 6 Soft Development
9 8 Inner Development
10 2 facility
11 3 local business
12 3 Foreign business
13 7 PS UI
14 8 SAP
15 8 JAVA

------------按照递归显示纪录(方法一)
declare @Dept table (id int, parent_id int, name char(30), level int, sort varchar(200))
declare @level  int
set @level = 0
insert @Dept 
select 
	id, parent_id, name, @level as level,
	CAST('/' + name + '/' AS varchar(200)) as sort 
from Dept where parent_id is null
	while @@rowcount > 0

begin
	set @Level = @Level + 1
	insert @Dept 
	select 
		a.id, a.parent_id, a.name, @Level as level, 
		(b.sort + a.name + '/' )  as sort 
	from Dept a
		inner join @Dept b
		on a.parent_id = b.id where b.level = @Level - 1
end
select * from @Dept order by sort

------------按照递归显示纪录(方法二)
WITH CTE AS(
    SELECT
        id,
        parent_id,
		name,
        depth = 0,
        path = CAST('/' + name + '/' AS varchar(200))
    FROM Dept
    WHERE parent_id IS NULL 
    UNION ALL
    SELECT
        e.id,
        e.parent_id,
		e.name,
        depth = mgr.depth + 1,
        path = CAST(mgr.path + e.name + '/' AS varchar(200))
    FROM Dept e
    INNER JOIN CTE mgr
    ON e.parent_id = mgr.id 
)
SELECT * FROM CTE ORDER BY path

0	All	/All/
2	Admin Dept	/All/Admin Dept/
10	facility	/All/Admin Dept/facility/
3	Business Dept	/All/Business Dept/
12	Foreign business	/All/Business Dept/Foreign business/
11	local business	/All/Business Dept/local business/
1	Financle Dept	/All/Financle Dept/
4	Serveice Dept	/All/Serveice Dept/
6	MIS	/All/Serveice Dept/MIS/
8	Soft Development	/All/Serveice Dept/MIS/Soft Development/
9	Inner Development	/All/Serveice Dept/MIS/Soft Development/Inner Development/
15	JAVA	/All/Serveice Dept/MIS/Soft Development/JAVA/
14	SAP	/All/Serveice Dept/MIS/Soft Development/SAP/
7	UI	/All/Serveice Dept/MIS/UI/
13	PS UI	/All/Serveice Dept/MIS/UI/PS UI/
5	Sale Dept	/All/Serveice Dept/Sale Dept/