有点难度的表组合计算，有耐心者进！200分!

Question

我实在做不出来才请教大家，希望大家能帮忙，上面催得紧；思路一看就会清楚，测试数据我写出来。200分，如果分不够我可以再加。
有表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-00000-00010          0.5                          G
20-78495-00080     半成品80      22-00000-00020          0.8                          PCS
20-78495-00100     半成品10      21-00000-00010          1.6                          G
20-78495-00100     半成品10      22-00000-00020          2.0                          PCS
90-78495-00410     半成品410     20-78495-00080          2.5                          PCS我想要的结果是：PARENT             ITEM_CODE       LOCATION    P_QTY     STOCK_QTY     QTY_RATIO     CTM_QTY     BOM_UNIT
20-78495-00080   21-00000-00010      CSP1       -50         -25           0.5          -12.5       G
20-78495-00080   22-00000-00020      CSP1       -50         -40           0.6          -24         PCS
20-78495-00100   21-00000-00010      CSP1       500         800           0.5          400         G
20-78495-00100   22-00000-00020      CSP1       500         1000          0.6          600         PCS
90-78495-00410   21-00000-00010      CSP1       1600        800           0.5          400         G
90-78495-00410   22-00000-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-00000-00010','0.5','G')
insert into BOM1(PARENT,DES,ITEM_CODE,QTY_PER,BOM_UNIT) 
values('20-78495-00080','半成品80','21-00000-00020','0.8','PCS')
insert into BOM1(PARENT,DES,ITEM_CODE,QTY_PER,BOM_UNIT) 
values('20-78495-00100','半成品10','21-00000-00010','1.6','G')
insert into BOM1(PARENT,DES,ITEM_CODE,QTY_PER,BOM_UNIT) 
values('20-78495-00100','半成品10','21-00000-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

create function getchrd(@PARENT varchar(50))
returns @t table (ITEM_CODE varchar(50),PARENT varchar(50),level int)
as
begin
declare @i int
set @i=1
insert into @t
select *,@i from BOM1 where PARENT=@PARENT
while @@rowcount>0
begin
set @i=@i+1
insert into @t
select a.*,@i from t a left join @t b on a.PARENT=b.ITEM_CODE
where b.level=@i-1
end
return
end
go
declare @PARENT varchar(50)
create table #results(PARENT  varchar(50),ITEM_CODE varchar(50))
declare my_cursor cursor for
  Select PARENT From BOM1
  open my_cursor
  fetch next from my_cursor into @PARENT
  while(@@fetch_status=0)
  begin
  if(left(@PARENT,2)='90')
  begin
insert into #results
select a.PARENT,b.ITEM_CODE
from BOM1 a,
 (select ITEM_CODE from dbo.getchrd(@PARENT)
  where level=2) b
where a.ITEM_CODE=b.ITEM_CODE and a.PARENT=@PARENT
  end
  else
  begin
  insert into #results
  select PARENT,ITEM_CODE
  from BOM1 where PARENT=@PARENT
  end 
  Fetch Next From my_cursor InTo @PARENT
  end
  close my_cursor 
  deallocate my_cursor  select * from #results
select a.PARENT,a.ITEM_CODE ,
(select LOCATION from ÊÓͼ1 b where a.ITEM_CODE=b.ITEM_CODE) as LOCATION,
(select STOCK_QTY from ÊÓͼ1 b where a.ITEM_CODE=b.ITEM_CODE) as P_QTY,
(select b.STOCK_QTY*c.QTY_PER from ÊÓͼ1 b,BOM1 c where a.ITEM_CODE=b.ITEM_CODE and a.PARENT=c.PARENT) as STOCK_QTY,
(select QTY_RATIO from ÊÓͼ1 b where a.ITEM_CODE=b.ITEM_CODE) as QTY_RATIO,
(select CTM_QTY from ÊÓͼ1 b where a.ITEM_CODE=b.ITEM_CODE) as CTM_QTY,
(select BOM_UNIT from BOM1 c where a.PARENT=c.PARENT) as BOM_UNIT
from #results a 

Answer 3

是的，我写错了，不好意思，BOM1和结果中的00000是78495

Answer 4

不好意思 我没有MS sql的环境 呵呵 

Answer 5

消息 213，级别 16，状态 1，过程 getchrd，第 7 行
插入错误: 列名或所提供值的数目与表定义不匹配。消息 208，级别 16，状态 1，第 13 行
对象名  'dbo.getchrd' 无效。

Answer 6

结果里的 P_QTY 怎么来的。！

Answer 7

90-78495-00410   21-00000-00010      CSP1       1600        800           0.5          400         G
90-78495-00410   22-00000-00020      CSP1       1600        1280          0.6          768         PCS 这两个数据不对吧！ BOM1表里 90-78495-00410 的 QTY_PER 是2.5

Answer 8

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 into #t1
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'--select * from #t1
--select * from BOM1
select a.PARENT,a.DES,b.ITEM_CODE,b.QTY_PER,b.BOM_UNIT  into #t2
 from BOM1 a inner join BOM1 b on a.ITEM_CODE =b.PARENT--select * from #t2select * into #BOM from BOM1
 delete a  from #BOM a inner join #t2 on a.PARENT=#t2.PARENT
 select * into #3 from 
(
select * from #BOM
union all 
select * from #t2
) a -- select * from #3
select a.PARENT,b.ITEM_CODE,b.LOCATION,b.STOCK_QTY as  P_QTY,b.STOCK_QTY*a.QTY_PER as STOCK_QTY,
b.QTY_RATIO,b.STOCK_QTY*a.QTY_PER *b.QTY_RATIO as CTM_QTY,a.BOM_UNIT   from  #3 a inner join #t1 b on a.ITEM_CODE=b.ITEM_CODE
drop table #t1
drop table #t2
drop table #3
drop table #BOM

Answer 9

--更正一下楼主的演示数据
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-00020','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')--如果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 10

要是 BOM1 的层次多了就不能那么弄了、 3层的话可以按照12 14那么做的

Answer 11

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