另开一贴，BOM的递归计算。

Question

有表tb1，tb2组成如下视图（简称视图1）（有查询条件,ITEM_CODE不会重复）:ITEM_CODE        LOCATION      STOCK_QTY      QTY_RATIO      CTM_QTY
20-78495-00080     CSP1         -50              0.15          -7.5
20-78495-00100     CSP1         500              0.25          125
90-78495-00410     CSP1         1600             0.65          1040
21-78495-00010     CSP1         100              0.5            50
22-78495-00020     CSP1         200              0.6            120视图1的由以下SQL语句组成：select a.ITEM_CODE,a.LOCATION,a.STOCK_QTY-ISNULL(B.SUMQTY,0) STOCK_QTY,A.QTY_RATIO,
(a.STOCK_QTY-ISNULL(B.SUMQTY,0))*a.QTY_RATIO CTM_QTY
from tb1 a left join 
(SELECT ITEM_CODE,LOCATION,ROUND(SUM(QUANTITY),6) AS SUMQTY 
FROM tb2
WHERE INDATE > '20110514 00:00:00.000' 
AND CHARINDEX(','+LOCATION+',',','+'CSP1'+',') > 0
AND ITEM_CODE BETWEEN '20-68184-17020' AND '96-00540-01010'
GROUP BY ITEM_CODE,LOCATION ) b 
on a.ITEM_CODE = b.ITEM_CODE 
where a.ITEM_CODE BETWEEN '20-08184-17020' AND '96-00540-01010'
and a.LOCATION='CSP1'表：BOM1；其中PARENT与ITEM_CODE可以存在2层或者3层的递归关系，90下面是20,20下面是21和22（21和22是最低层）。PARENT              DES           ITEM_CODE            QTY_PER(指ITEM_CODE的重量)   BOM_UNIT
20-78495-00080     半成品80      21-78495-00010          0.5                          G
20-78495-00080     半成品80      22-78495-00020          0.8                          PCS
20-78495-00100     半成品10      21-78495-00010          1.6                          G
20-78495-00100     半成品10      22-78495-00020          2.0                          PCS
90-78495-00410     半成品410     20-78495-00080          2.5                          PCS视图1和BOM1进行计算，我想要的结果是：PARENT             ITEM_CODE       LOCATION    P_QTY     STOCK_QTY     QTY_RATIO     CTM_QTY     BOM_UNIT
20-78495-00080   21-78495-00010      CSP1       -50         -25           0.5          -12.5       G
20-78495-00080   22-78495-00020      CSP1       -50         -40           0.6          -24         PCS
20-78495-00100   21-78495-00010      CSP1       500         800           0.5          400         G
20-78495-00100   22-78495-00020      CSP1       500         1000          0.6          600         PCS
90-78495-00410   21-78495-00010      CSP1       1600        800           0.5          400         G
90-78495-00410   22-78495-00020      CSP1       1600        1280          0.6          768         PCS  
其中P_QTY=视图1中的STOCK_QTY, STOCK_QTY=P_QTY*BOM1对应QTY_PER，CTM_QTY=STOCK_QTY*QTY_RATIO；数据库环境：sql 2000

Answer 1

测试数据：tb1:
create table tb1(ITEM_CODE varchar(50),LOCATION varchar(50),STOCK_QTY float,
QTY_RATIO float,CTM_QTY float)insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 
values('20-78495-00080','CSP1','200','0.15','30')
insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 
values('20-78495-00100','CSP1','500','0.25','125')
insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 
values('90-78495-00410','CSP1','1000','0.65','650')
insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 
values('21-78495-00010','CSP1','100','0.5','50')
insert into tb1(ITEM_CODE,LOCATION,STOCK_QTY,QTY_RATIO,CTM_QTY) 
values('22-78495-00020','CSP1','200','0.6','120')tb2:
create table tb2(ITEM_CODE varchar(50),LOCATION varchar(50),
QUANTITY float,INDATE datetime)
insert into tb2(ITEM_CODE,LOCATION,QUANTITY,INDATE) 
values('20-78495-00080','CSP1','150','2011-05-01')
insert into tb2(ITEM_CODE,LOCATION,QUANTITY,INDATE) 
values('20-78495-00080','CSP1','250','2011-05-19')
insert into tb2(ITEM_CODE,LOCATION,QUANTITY,INDATE) 
values('20-78495-00100','CSP1','100','2011-05-04')
insert into tb2(ITEM_CODE,LOCATION,QUANTITY,INDATE) 
values('90-78495-00410','CSP1','-500','2011-05-05')
insert into tb2(ITEM_CODE,LOCATION,QUANTITY,INDATE) 
values('90-78495-00410','CSP1','-600','2011-05-25')BOM1:
Create table BOM1(PARENT varchar(50),DES varchar(50),ITEM_CODE varchar(50),
QTY_PER float,BOM_UNIT varchar(50))
insert into BOM1(PARENT,DES,ITEM_CODE,QTY_PER,BOM_UNIT) 
values('20-78495-00080','半成品80','21-78495-00010','0.5','G')
insert into BOM1(PARENT,DES,ITEM_CODE,QTY_PER,BOM_UNIT) 
values('20-78495-00080','半成品80','22-78495-00020','0.8','PCS')
insert into BOM1(PARENT,DES,ITEM_CODE,QTY_PER,BOM_UNIT) 
values('20-78495-00100','半成品10','21-78495-00010','1.6','G')
insert into BOM1(PARENT,DES,ITEM_CODE,QTY_PER,BOM_UNIT) 
values('20-78495-00100','半成品10','22-78495-00010','2.0','PCS')
insert into BOM1(PARENT,DES,ITEM_CODE,QTY_PER,BOM_UNIT) 
values('90-78495-00410','半成品410','20-78495-00080','2.5','PCS')

Answer 2

2000的 BOM  等有时间的兄弟们来写

Answer 3

--如果BOM中最多只有三层的话
SELECT a.PARENT,a.ITEM_CODE,b.LOCATION,b.STOCK_QTY P_QTY,b.STOCK_QTY*a.QTY_PER STOCK_QTY,c.QTY_RATIO,b.STOCK_QTY*a.QTY_PER*c.QTY_RATIO CTM_QTY,a.BOM_UNIT 
FROM (
    SELECT a.PARENT,ISNULL(b.ITEM_CODE,a.ITEM_CODE) ITEM_CODE,ISNULL(b.QTY_PER,a.QTY_PER) QTY_PER,ISNULL(b.BOM_UNIT,a.BOM_UNIT) BOM_UNIT 
    FROM BOM1 a 
    LEFT JOIN BOM1 b ON a.ITEM_CODE = b.PARENT
) a 
JOIN 视图1 b ON a.PARENT = b.ITEM_CODE
JOIN 视图1 c ON a.ITEM_CODE = c.ITEM_CODE/*
PARENT    ITEM_CODE    LOCATION    P_QTY    STOCK_QTY    QTY_RATIO    CTM_QTY    BOM_UNIT
20-78495-00080    21-78495-00010    CSP1    -50    -25    0.5    -12.5    G
20-78495-00080    22-78495-00020    CSP1    -50    -40    0.6    -24    PCS
20-78495-00100    21-78495-00010    CSP1    500    800    0.5    400    G
20-78495-00100    22-78495-00020    CSP1    500    1000    0.6    600    PCS
90-78495-00410    21-78495-00010    CSP1    1600    800    0.5    400    G
90-78495-00410    22-78495-00020    CSP1    1600    1280    0.6    768    PCS
*/

Answer 4

测试数据没有问题，但是放到项目上就有问题了，没有一条数据显示。视图1里面都是有很多数据的，而且BOM1里面也是有很多数据的。问题可能出在那里呢？