[MSSQL]BOM反查

通常我们会写一些展BOM的程序，用游标实现。但是我们在做ECN变更的时候，经常需要查询一个物料的上级半成品或者顶层的成品有哪些？

下面SQL就是解决这个问题的方法之一：

1.现在有一个BOM表(表名:ProductStructures),有两个字段:cPSPCode(子件对应的母件编号),cPSCode(子件编号),举例数据如下:
SQL code
/*
cPSPCode cPSCode
   A       B
   B       C
   AA      BB
   BB      C
   AAA     BBB
   BBB      C
*/



2.由于一个子件如上的C可以对应多个母件,现想求一自定义函数,当输入C时反查出其对应的上级母件至最顶层母件,要求得出的结果如下:
SQL code
/*
cPSPCode cPSCode
   C       BBB
   BBB     AAA
    C       BB
   BB       AA
    C        B
    B        A
*/






-------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
CREATE TABLE [tb] (cPSPCode VARCHAR(3),cPSCode VARCHAR(3))
INSERT INTO [tb]
SELECT 'A','B' UNION ALL
SELECT 'B','C' UNION ALL
SELECT 'AA','BB' UNION ALL
SELECT 'BB','C' UNION ALL
SELECT 'AAA','BBB' UNION ALL
SELECT 'BBB','C'

--SQL查询如下:

GO

CREATE FUNCTION dbo.GetPSCode(@cPSCode VARCHAR(10))
    RETURNS @t TABLE(cPSCode VARCHAR(10),
                     cPSPCode VARCHAR(10),
                     Sort VARCHAR(1000),
                     level INT)
AS
BEGIN
    DECLARE @level INT;
    SET @level = 1;
    
    INSERT @t
        SELECT
            cPSCode,
            cPSPCode,
            cPSPCode,
            @level
        FROM tb 
        WHERE cPSCode = @cPSCode;
        
    WHILE @@ROWCOUNT>0
      BEGIN
          SET @level=@level+1;
          
          INSERT @t
              SELECT
                  B.cPSCode,
                  B.cPSPCode,
                  A.Sort + '.' + B.cPSPCode,
                  @level
              FROM @t AS A
                  JOIN tb AS B
                      ON A.cPSPCode=B.cPSCode
                          AND A.level=@level-1;
      END
    RETURN
END
GO

SELECT cPSCode,cPSPCode FROM dbo.GetPSCode('C') ORDER BY Sort

GO
DROP TABLE tb;
DROP FUNCTION dbo.GetPSCode

/*
cPSCode    cPSPCode
---------- ----------
C          B
B          A
C          BB
BB         AA
C          BBB
BBB        AAA

(6 行受影响)
*/
用2005就比较简单了

-->> Author:  让你望见影子的墙(HEROWANG)生成测试数据  Date:2009-04-17 22:30:27
IF OBJECT_ID('tb') IS NOT NULL 
    DROP TABLE tb
Go
CREATE TABLE tb(cpspcode NVARCHAR(3),cpscode NVARCHAR(3))
Go
INSERT INTO tb
 SELECT 'A','B' UNION ALL
 SELECT 'B','C' UNION ALL
 SELECT 'AA','BB' UNION ALL
 SELECT 'BB','C' UNION ALL
 SELECT 'AAA','BBB' UNION ALL
 SELECT 'BBB','C' 
GO

SELECT     * FROM    TB
;
with 
wang as (select row=row_number() over (order by getdate()),* from tb where cpscode='c'     
          union all
         select wang.row ,tb.*from tb ,wang where tb.cpscode =wang.cpspcode 
 )

select cpscode,cpspcode from wang
order by row

cpscode    cpspcode
C    B
B    A
BB    AA
C    BB
C    BBB
BBB    AAA