分类汇总数据问题！！！

[dewe213]

有2张表结构如下：

部门代码表(bmdm)：

部门代码 部门名称
0100 销售科
0101 销售科一部
0102 销售科二部
0200 市场科
0201 市场科一部
0203 市场科三部


销售表(xs)：

部门代码 销售金额
0100 100
0101 80
0102 30
0200 110
0201 60
0203 70


需要实现的结果表:(显示按部门分类汇总和明细数据，如：销售科=本身+销售一部+二部数据)

部门名称 销售金额
销售科 210
销售科一部 80
销售科二部 30
市场科 240
市场科一部 60
市场科三部 70

[dewe213]

在oracle环境中如何实现？
right(a.部门代码,2) 函数oracle中不支持

[liangCK]

查oracle函数.或到oracle版问.

[liangCK]

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)
*/

[dewe213]

不用中间表，只用sql语句能实现吗？

[tim_spac]

向这样的层级编码模式，最好设计为:
部门代码 部门名称
01 销售科
0101 销售科一部
0102 销售科二部
02 市场科
0201 市场科一部
0203 市场科三部
然后:

select [部门名称],
  [销售金额] = (select sum([销售金额]) from xs b
               where b.[部门代码] like a.[部门代码]+'%')
from bmdm a

[wxg22526451]

没有父子ID的关联？

[liangCK]

--> liangCK小梁 于2008-10-07
--> 生成测试数据: #bmdm
IF OBJECT_ID('tempdb.dbo.#bmdm') IS NOT NULL DROP TABLE #bmdm
CREATE TABLE #bmdm (部门代码 VARCHAR(4),部门名称 NVARCHAR(10))
INSERT INTO #bmdm
SELECT '0100','销售科' UNION ALL
SELECT '0101','销售科一部' UNION ALL
SELECT '0102','销售科二部' UNION ALL
SELECT '0200','市场科' UNION ALL
SELECT '0201','市场科一部' UNION ALL
SELECT '0203','市场科三部'
--> liangCK小梁 于2008-10-07
--> 生成测试数据: #xs
IF OBJECT_ID('tempdb.dbo.#xs') IS NOT NULL DROP TABLE #xs
CREATE TABLE #xs (部门代码 VARCHAR(4),销售金额 INT)
INSERT INTO #xs
SELECT '0100','100' UNION ALL
SELECT '0101','80' UNION ALL
SELECT '0102','30' UNION ALL
SELECT '0200','110' UNION ALL
SELECT '0201','60' UNION ALL
SELECT '0203','70'

--SQL查询如下:

SELECT a.部门代码,b.销售金额 
FROM #bmdm AS a
 LEFT OUTER JOIN
  (SELECT 部门代码 ,
          CASE WHEN RIGHT(部门代码,2)='00' THEN 
                  (SELECT SUM(销售金额) FROM #xs
                     WHERE 部门代码 LIKE LEFT(x.部门代码,2)+'%')
               ELSE 销售金额
          END AS 销售金额
   FROM #xs AS x
  ) AS b
ON a.部门代码=b.部门代码 

/*
部门代码 销售金额
---- -----------
0100 210
0101 80
0102 30
0200 240
0201 60
0203 70

(6 行受影响)
*/

[chuifengde]

select 部门名称,销售金额=case when right(a.部门代码,2)='00' then (select sum(销售金额) from xs where left(部门代码,2)=left(a.部门代码,2)) else sum(销售金额) end
from bmdm a inner join xs b
on a.部门代码=b.部门代码
group by a.部门代码,部门名称

[wer123q]

select a.部门名称 , 销售金额=case when b.部门代码 = 0100 then (select sum(销售金额) from xs where left(b.部门代码,2)=01 ) 
when b.部门代码 = 0200 then (select sum(销售金额) from xs where left(b.部门代码,2)=02 )
else b.销售金额
end  
from bmdm a inner join xs b 
on a.部门代码 = b.部门代码

[ironxiao]

create table bmdm (id varchar(4),name varchar(10))
insert into bmdm(id,name)
(select '0100','销售科' union all
select '0101','销售科一部' union all
select '0102','销售科二部' union all
select '0200','市场科' union all
select '0201','市场科一部' union all
select '0203','市场科三部')
create table xs (id varchar(4),Account float)
insert into xs
select '0100','100' union all
select '0101','80' union all
select '0102','30' union all
select '0200','110' union all
select '0201','60' union all
select '0203','70'
select a.id,a.name,account=case when right(id,2)='00' then (select sum(Account) from xs b where left(b.id,2)=left(a.id,2))
else (select sum(Account) from xs b where a.id=b.id)
end
from bmdm a