sql 2005 树项统计

Question

我相信好多程序员朋友都遇到过核算公司BOM，求一个成品的单价的问题。在CSDN上，也有许多朋友发贴问这样的问题。我为我们公司开发了许多报表，涉及到各个部门，当然也包括采购部门（采购经理要看），那核算BOM成品单价也是理所当然的事了。其实核算一个BOM成品的算法不是很难,但要以树型的方式来显示就有点难度了。在我们公司，我写了两个报表一个是单层的（老板要看）,另一个是多层的也就是树型结构（工程部，采购部要看）。老板看单层，是因为他只关心结果，只要看一个成品下面第一层每个半成品共要多少钱就可以了。而工程部可不同了，他们可要看到一个层次结构。下面我以这个BOM为例，详细叙述如何核算BOM及层次显示。Follow Me!                              FG001                               | --------------------------------------------------------------------- |                                  |                                |SFG001                           SFG002                           SFG003 |                                   |                                   -------------------               ---------------------                  |                 |               |            |      |WIP001         WIP002           WIP003       WIP004 WIP005       WIP006|                 |               |RAW001 RAW002  RAW003      RAW004,RAW005  ............|                             |KKK001                         KKK003|WWW005 一:先创建BOM表 create table t
(parent varchar(10),
 child varchar(10),qty numeric(9,2)
)insert into t
select 'FG001',  'SFG001',     1  union all
select 'FG001' , 'SFG002',     1 union all
select 'FG001'  ,'SFG003',     1 union all
select 'SFG001', 'WIP001',     2 union all
select 'SFG001' ,'WIP002',     2 union all
select 'SFG002' ,'WIP003',     3 union all
select 'SFG002' ,'WIP004',     3 union all
select 'SFG002' ,'WIP005',     2 union all
select 'SFG003' ,'WIP006',     3 union all
select 'WIP001' ,'RAW001',     2.66 union all
select 'WIP001' ,'RAW002'  ,   2.33 union all
select 'WIP002' ,'RAW003'  ,   3.21 union all
select 'WIP003' ,'RAW004'  ,   1.89 union all
select 'WIP003' ,'RAW005'  ,   1.86 union all
select 'RAW001','KKK001',      3.25 union all
select 'RAW004','KKK003',      4.26 union all
select 'KKK001','WWW005',      5.23
 二:创建函数（a:树型结构显示）  
create function test(@parent VARCHAR(10))
returns @t table(parent Nvarchar(10),child Nvarchar(10),qty numeric(9,2),
                level int,sort Nvarchar(1000)collate Latin1_General_BIN  ) 
as
begin
     declare @level int
     set @level=1
     insert into @t
     select parent,child,qty,@level,parent+child 
     from t
     where parent=@parent collate Latin1_General_BIN 
     while @@rowcount>0
     begin
         set @level=@level+1
         insert @t
         select a.parent,a.child,a.qty*b.qty,@level,b.sort+a.child
         from   t a ,@t b
         where  a.parent=b.child collate Latin1_General_BIN
                and b.level=@level-1   
    end
return
end--调用函数
select
    level,
    space(level*2)+'|--' + child as 'product',
    qty
from 
    dbo.test('FG001')
order by 
    sort 
--结果
/**//*
level       product                         qty         
1             |--SFG001                     1.00
2               |--WIP001                   2.00
3                 |--RAW001                 5.32
4                   |--KKK001               17.29
5                     |--WWW005             90.43
3                 |--RAW002                 4.66
2               |--WIP002                   2.00
3                 |--RAW003                 6.42
1             |--SFG002                     1.00
2               |--WIP003                   3.00
3                 |--RAW004                 5.67
4                   |--KKK003               24.15
3                 |--RAW005                 5.58
2               |--WIP004                   3.00
2               |--WIP005                   2.00
1             |--SFG003                     1.00
2               |--WIP006                   3.00(17 row(s) affected)
*/

Answer 1

主要问题是一个树型的数据统计问题   
    
  有一个数据表，结构和数据如下：   
  pkid      upNode（父节点,0为根接点）      quantity1          quantity2         quantity3
    1                  0                                             
    2                  0                                             
    3                  1                                             
    4                  3                       10             10                10   
    5                  3                       20             20                20   
    4                  1                       30             30                30   
    5                  2                                             
    6                  5                       10             10                10   
    7                  5                       20             20                20   
                                     
  希望得到：                                    
                                                                       
    1                  0                       60             60                60   
    2                  0                       30             30                30   
    3                  1                       30             30                30   
    4                  3                       10             10                10   
    5                  3                       20             20                20   
    4                  1                       30             30                30   
    5                  2                       30             30                30   
    6                  5                       10              10                10   
    7                  5                       20                20                 20   
    
  如何写这段查询语句，或者说是怎么实现，利用试图也可以 

Answer 2

2005?
2000?2005估计可以使用CTE实现,

Answer 3

to : liangCK 我的报表是还要统计多列哦    quantity1          quantity2        quantity3 

Answer 4

 自己解决了,DROP TABLE temp
CREATE TABLE temp(fid INT ,pid INT ,value1 INT ,value2 INT, value3 int)
INSERT temp SELECT 1,0,null,null,null UNION ALL
 SELECT 2,0,null,null,null UNION ALL
 SELECT 3,1,null,null,null UNION ALL
 SELECT 4,2,5,5,5 UNION ALL
 SELECT 5,3,10,10,10 UNION ALL
 SELECT 6,3,20,20,20 UNION ALL
 SELECT 7,1,30,30,30 UNION ALL
 SELECT 8,1,null,null,null UNION ALL
 SELECT 9,7,40,40,40 UNION ALL
 SELECT 10,7,50,50,30 
 create    function   f_calc
(@pkid int)   
returns  @t table(fid int,level int)
as
begin
declare   @l   int   
set   @l=0   
insert   @t   select   @pkid,@l   
  
while   @@rowcount>0   
  begin   
  set   @l=@l+1   
  insert   @t   select   a.fid,@l   
  from   temp   a   join   @t   b   on   a.pid=b.fid   
  where   b.level=@l-1   
  end
return
end
GOSELECT 
fid,pid,
value1=(SELECT SUM(value1) FROM temp AS t WHERE fid IN (SELECT fid FROM f_calc(a.fid))),
value2=(SELECT SUM(value2) FROM temp AS t WHERE fid IN (SELECT fid FROM f_calc(a.fid))),
value3=(SELECT SUM(value3) FROM temp AS t WHERE fid IN (SELECT fid FROM f_calc(a.fid)))
FROM  temp AS aSELECT * FROM tempDROP TABLE temp
DROP FUNCTION f_calc