SQL存储过程，(高手进)
表BOMT产品P_CODE 物料 CODE 编号 BOMNOKJ-1501-024 KJ-1501-024 KJ-1501-024KJ-1501-024 ZA150102401 KJ-1501-024ZA150102401 ZC150103101 ZA150102401ZC150103101 PDA00082300 ZC150103101PDA00082300 PBA00082300 PDA00082300PBA00082300 POA00082300 PBA00082300POA00082300 PMA01002000 POA00082300ZA150102401 ZC150101300 ZA150102401ZC150101300 POA02082200 ZC150101300POA02082200 PMA01002000 POA02082200PMA01002000 PMB00000100 PMA01002000根据编号BOMNO KJ-1501-024 找到它的下接。要得到显示层次他们关系是 CODE的上接是P_CODE，看下面给的数据就知道了。（是物料清单那样的模式）层次 产品 物料1 KJ-1501-024 KJ-1501-024 2 KJ-1501-024 ZA150102401 3 ZA150102401 ZC150103101 4 ZC150103101 PDA00082300 5 PDA00082300 PBA00082300 6 PBA00082300 POA00082300 7 POA00082300 PMA01002000 3 ZA150102401 ZC150101300 4 ZC150101300 POA02082200 5 POA02082200 PMA01002000 5 PMA01002000 PMB00000100以前别人给写过一个，但是部理想，因为层次显示不清楚。是乱的。也发问过，也有高手给过相关的资料，但是鄙人没拿才华，没理解。所以才重新发帖，希望各位帮帮忙。（大号没分，所以申请小号发帖）如果需求说的不是很明确的话，还请各位发问。谢谢！
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
[解决办法]