如何让一个表返回树结构的数据
ID NO ParentNO
1BA
2CA
4B1B
5B2B
6C1C
7C1-1C1
比如我查询A返回
B,C,B1,B2,C1,C-1
[解决办法]
--树形数据编号重排示例--测试数据DECLARE @t TABLE(No varchar(10))INSERT @t SELECT '1'UNION ALL SELECT '3'UNION ALL SELECT '302'UNION ALL SELECT '305'UNION ALL SELECT '305001'UNION ALL SELECT '305005'UNION ALL SELECT '6'UNION ALL SELECT '601'--重排编码UPDATE a SET No=RIGHT(--重排第一层编码 (SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND LEN(No)=1),1) +CASE --重排第二层编码 WHEN LEN(No)>1 THEN RIGHT(100+ (SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,1)+'__'),2) ELSE '' END +CASE --重排第三层编码 WHEN LEN(No)>3 THEN RIGHT(1000+ (SELECT COUNT(DISTINCT No) FROM @t WHERE No<=a.No AND No LIKE LEFT(a.NO,3)+'___'),3) ELSE '' ENDFROM @t a--显示处理结果SELECT * FROM @t/*--结果No ---------- 122012022020012020023301--*/
[解决办法]
-- 树形数据层次显示处理示例--测试数据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/*--结果|--山东省 |--烟台市 |--招远市 |--青岛市|--四会市 |--清远市 |--小分市--*/
[解决办法]
-- 树形数据深度排序处理示例(递归法)--测试数据CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))INSERT tb 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','小分市'GO--广度搜索排序函数CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)RETURNS @t_Level TABLE(ID char(3),sort int)ASBEGIN DECLARE tb CURSOR LOCAL FOR SELECT ID FROM tb WHERE PID=@ID OR(@ID IS NULL AND PID IS NULL) OPEN TB FETCH tb INTO @ID WHILE @@FETCH_STATUS=0 BEGIN INSERT @t_Level VALUES(@ID,@sort) SET @sort=@sort+1 IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层) BEGIN --递归查找当前节点的子节点 INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort) SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数 END FETCH tb INTO @ID END RETURNENDGO--显示结果SELECT a.*FROM tb a,f_Sort(DEFAULT,DEFAULT) bWHERE a.ID=b.IDORDER BY b.sort/*--结果ID PID Name ------ --------- ---------- 001 NULL 山东省002 001 烟台市004 002 招远市003 001 青岛市005 NULL 四会市006 005 清远市007 006 小分市--*/
[解决办法]
列子:
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx
[解决办法]
--网上列子一堆，还是给楼主的数据写一下列子..create table T(ID int, [NO] nvarchar(10), ParentNO nvarchar(10))insert T select 1, 'B', 'A' insert T select 2, 'C', 'A' insert T select 4, 'B1', 'B' insert T select 5, 'B2', 'B' insert T select 6, 'C1', 'C' insert T select 7, 'C1-1', 'C1' go--drop table Tcreate function F_tree(@ParentNO nvarchar(5))returns nvarchar(100)asbegindeclare @s nvarchar(100),@i intdeclare @T table([NO] nvarchar(5),[ParentNO] nvarchar(5),lev int)set @i=0insert @T select [NO],[ParentNO],@i from T where ParentNO=@ParentNOwhile @@rowcount>0 begin set @i=@i+1 insert @T select a.[NO],a.[ParentNO],@i from T a join @T b on a.ParentNO=b.[NO] and b.lev=@i-1 endselect @s=isnull(@s+',','')+[NO] from @Treturn @sendgoselect dbo.F_tree('A') ---------------------------------------------------------------- B,C,B1,B2,C1,C1-1（所影响的行数为 1 行）
[解决办法]
--用一临时表保存中间结果.与层次declare @tmp table (layer int,no varchar(10))declare @layer int,@no varchar(10)set @no='A'set @layer=0if exists(select * from 表 where parentno=@no) insert into @tmp (layer,no) select @layer,no from 表 where parentno=@nowhile exists(select * from 表 a,@tmp b where a.parentno=b.no and b.layer=@layer)begin insert into @tmp (layer,no) select @layer+1,a.no from 表 a,@tmp b where a.parentno=b.no and b.layer=@layer set @layer=@layer+1endselect * from @tmp