SQL存储过程显示树形菜单
- SQL code
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[P_DJP](@deeplevel int,@firstcode nvarchar(100),@liqty int,@gd nvarchar(100))ASSET NOCOUNT ONSET XACT_ABORT ONBEGINcreate table #tree (bomno nvarchar(100),code nvarchar(100), deeplevel int, cbdesc nvarchar(100), qty_nee numeric(19,8), loc varchar(32),wastage numeric(6,2),liqty numeric(19,8),sh numeric(19,8), isLeafnode int,tree nvarchar(max) default '' ) declare @cbdesc varchar(32), @QTY_NEED numeric(19,8), @loc varchar(32), @wastage numeric(6,2)insert #tree select BOMT.BOMNO,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((@liqty+(@liqty*(WASTAGE/100)))*QTY_NEED),(WASTAGE/100),1,BOMT.CODE + left('00000000000000000000',20-len(BOMT.CODE)) from BOMT left JOIN MAINBOM on BOMT.CODE=MAINBOM.BOMNO where BOMT.BOMNO=@firstcode WHILE @@rowcount > 0 BEGIN SET @deeplevel = @deeplevel + 1 update #tree set isLeafnode= 0 from #tree join BOMT on #tree.deeplevel=@deeplevel-1 and BOMT.BOMNO collate database_default =#tree.code insert #tree select @firstcode,BOMT.CODE,@deeplevel,BOMT.CBDESC,BOMT.QTY_NEED,MAINBOM.LOC,BOMT.WASTAGE,ceiling((liqty+(liqty*(WASTAGE/100)))*QTY_NEED),sh+(WASTAGE/100),1,#tree.tree+'_'+BOMT.CODE+left('00000000000000000000',20-len(BOMT.CODE)) from BOMT join #tree on #tree.deeplevel=@deeplevel-1 and BOMT.BOMNO collate database_default =#tree.code left join MAINBOM on BOMT.CODE=MAINBOM.BOMNOENDselect space((deeplevel-1)*2)+cast(deeplevel as varchar),code,cbdesc,qty_nee,loc,sh,liqty from #tree RETURN @@ERROR ENDSET NOCOUNT OFFSET XACT_ABORT OFF这是我的一个存储过程，但是现在有个问题就是在在显示层次的时候不是根据我需要的形式显示的。
我需要显示的是
1
2
3
2
3
4
3
4
5
4
5
2
3
而它显示的层次关系是
1
2
2
3
3
3
4
4
4
5
5
高手望请教。
[解决办法]
一般树节点在数据库存储，有2种方式
一种是需要递归的：id，父id
一种是无须递归的：id，层次，在本子树里的序号
你的是哪一种？
[解决办法]
我记得上次帮你写过。。
http://topic.csdn.net/u/20110506/16/572420d1-3bc8-4e51-8aef-d29715fe09a8.html
[解决办法]
考,使用全路排序：
- SQL code
use tempdbGoif object_id('#') Is Not Null Drop Table #Create Table #( ID int , ParentID int, level smallint, value nvarchar(10)) Goinsert into #(ID,ParentID,level,value) Select 1,0,1,'Root' Union All Select 2,1,2,'A1' Union All Select 3,1,2,'B1' Union All Select 4,1,2,'C1' Union All Select 5,2,3,'A11' Union All Select 6,3,3,'B11' Union All Select 7,5,4,'A111' Union All Select 8,6,4,'B111' Union All Select 9,8,5,'B1111'Go;With CTE_Seq As( Select ID,ParentID,level,value,convert(nvarchar(200),rtrim(id)) As IDPath From # Where ParentID=0 Union All Select b.ID,b.ParentID,b.level,b.value,convert(nvarchar(200),a.IDPath+'.'+rtrim(b.id)) As IDPath From CTE_Seq As a Inner Join # As b On b.ParentID=a.ID )Select value,level,IDPath From CTE_Seq Order By IDPath/*value level IDPath---------- ------ ----------Root 1 1A1 2 1.2A11 3 1.2.5A111 4 1.2.5.7B1 2 1.3B11 3 1.3.6B111 4 1.3.6.8B1111 5 1.3.6.8.9C1 2 1.4*/
[解决办法]
- SQL code
--测试数据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 intSET @Level=0INSERT @t_Level SELECT ID,@Level,IDFROM @tWHERE PID IS NULLWHILE @@ROWCOUNT>0BEGIN 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-1END--显示结果SELECT SPACE(b.Level*2)+'|--'+a.NameFROM @t a,@t_Level bWHERE a.ID=b.IDORDER BY b.Sort/*--结果|--山东省 |--烟台市 |--招远市 |--青岛市|--四会市 |--清远市 |--小分市--*/
[解决办法]
- SQL code
BOM按节点排序应用实例 ---------------------------------------- Author : htl258(Tony)-- Date : 2010-04-23 02:37:28-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)-- Subject: BOM按节点排序应用实例-------------------------------------- --实例1:--> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb]GOCREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))INSERT [tb]SELECT 1,'01',0,N'服装' UNION ALLSELECT 2,'01',1,N'男装' UNION ALLSELECT 3,'01',2,N'西装' UNION ALLSELECT 4,'01',3,N'全毛' UNION ALLSELECT 5,'02',3,N'化纤' UNION ALLSELECT 6,'02',2,N'休闲装' UNION ALLSELECT 7,'02',1,N'女装' UNION ALLSELECT 8,'01',7,N'套装' UNION ALLSELECT 9,'02',7,N'职业装' UNION ALLSELECT 10,'03',7,N'休闲装' UNION ALLSELECT 11,'04',7,N'西装' UNION ALLSELECT 12,'01',11,N'全毛' UNION ALLSELECT 13,'02',11,N'化纤' UNION ALLSELECT 14,'05',7,N'休闲装'GO--SELECT * FROM [tb] -->SQL查询如下: ;WITH T AS( SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*, CAST(ID AS VARBINARY(MAX)) AS px FROM tb AS A WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid) UNION ALL SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*, CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)) FROM tb AS A JOIN T AS B ON A.pid=B.id)SELECT Code,Name FROM T ORDER BY px/*Code Name-------------------- ----------01 服装0101 男装010101 西装01010101 全毛01010102 化纤010102 休闲装0102 女装010201 套装010202 职业装010203 休闲装010204 西装01020401 全毛01020402 化纤010205 休闲装 (14 行受影响)*/ --实例2:--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb]GOCREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))INSERT [tb]SELECT 1,0,'test1' UNION ALLSELECT 2,0,'test2' UNION ALLSELECT 3,1,'test1.1' UNION ALLSELECT 4,2,'test2.1' UNION ALLSELECT 5,3,'test1.1.1' UNION ALLSELECT 6,1,'test1.2'GO--SELECT * FROM [tb]-->SQL查询如下:;WITH T AS( SELECT *,CAST(ID AS VARBINARY(MAX)) AS px FROM tb AS A WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid]) UNION ALL SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)) FROM tb AS A JOIN T AS B ON A.[parentid]=B.id)SELECT [id],[parentid],[categoryname] FROM T ORDER BY px/*id parentid categoryname----------- ----------- ------------1 0 test13 1 test1.15 3 test1.1.16 1 test1.22 0 test24 2 test2.1(6 行受影响)*/本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx
[解决办法]
learning, collecting