表BOMT

产品P_CODE    物料 CODE             编号  BOMNO
KJ-1501-024    KJ-1501-024    KJ-1501-024
KJ-1501-024    ZA150102401    KJ-1501-024
ZA150102401    ZC150103101    ZA150102401
ZC150103101    PDA00082300    ZC150103101
PDA00082300    PBA00082300    PDA00082300
PBA00082300    POA00082300    PBA00082300
POA00082300    PMA01002000    POA00082300
ZA150102401    ZC150101300    ZA150102401
ZC150101300    POA02082200    ZC150101300
POA02082200    PMA01002000    POA02082200
PMA01002000    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]

GO

CREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))

INSERT [tb]

SELECT 1,'01',0,N'服装' UNION ALL

SELECT 2,'01',1,N'男装' UNION ALL

SELECT 3,'01',2,N'西装' UNION ALL

SELECT 4,'01',3,N'全毛' UNION ALL

SELECT 5,'02',3,N'化纤' UNION ALL

SELECT 6,'02',2,N'休闲装' UNION ALL

SELECT 7,'02',1,N'女装' UNION ALL

SELECT 8,'01',7,N'套装' UNION ALL

SELECT 9,'02',7,N'职业装' UNION ALL

SELECT 10,'03',7,N'休闲装' UNION ALL

SELECT 11,'04',7,N'西装' UNION ALL

SELECT 12,'01',11,N'全毛' UNION ALL

SELECT 13,'02',11,N'化纤' UNION ALL

SELECT 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:

--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))
INSERT [tb]
SELECT 1,0,'test1' UNION ALL
SELECT 2,0,'test2' UNION ALL
SELECT 3,1,'test1.1' UNION ALL
SELECT 4,2,'test2.1' UNION ALL
SELECT 5,3,'test1.1.1' UNION ALL
SELECT 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))