通常我们会写一些展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