用SQL统计出父级和子级的数据,可能有3层或者4层以上的，为空是最上层的区域
区域 销售额 上级区域
001 100.0 005
002 200.0 005
003 100.0 005
004 100.0 005
005 200.0 01
01 50.0 sql
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-03-29 15:03:48
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([区域] varchar(3),[销售额] INT ,[上级区域] varchar(3))
insert [huang]
select '001',100.0,'005' union all
select '002',200.0,'005' union all
select '003',100.0,'005' UNION all
select '004',100.0,'005' union all
select '005',200.0,'01' union all
select '01',50.0,null
--------------开始查询--------------------------
;WITH cte AS --产生一个级别用于后续使用
(
SELECT 区域,销售额,[上级区域],0 AS [级别]
FROM huang
WHERE [上级区域] IS NULL
UNION ALL
SELECT b. 区域,b.销售额,b.[上级区域],a.级别+1 AS 级别
FROM cte a INNER JOIN huang b ON b.[上级区域]=a.[区域]
)
--计算非最低级别的总数，即需要汇总的数据
SELECT 区域,(SELECT SUM(销售额) FROM cte a WHERE b.级别<=a.级别 ),[上级区域]
FROM cte b
WHERE 级别<(SELECT MAX(级别) FROM cte)
UNION ALL
--最低级别的数据不汇总
SELECT 区域,销售额,[上级区域]
FROM cte b
WHERE 级别 IN (SELECT MAX(级别) FROM cte)
----------------结果----------------------------
/*
区域 上级区域
---- ----------- ----
01 750 NULL
005 700 01
001 100 005
002 200 005
003 100 005
004 100 005
*/
with tb(区域,销售额,上级区域)
as(
select '001',100.0,'005' union all
select '002',200.0,'005' union all
select '003',100.0,'005' union all
select '004',100.0,'01' union all
select '005',200.0,'01' union all
select '01',50.0,null
),
cte as(
select *,lv=cast(0 as int),qy=cast(','+区域+',' as varchar(2000)) from tb where 上级区域 is null union all
select t.*,lv=cast(c.lv+1 as int),qy=cast(c.qy+rtrim(t.区域)+',' as varchar(2000)) from tb t join cte c on t.上级区域=c.区域
)
select 区域,销售额,下级销售额=(select isnull(sum(销售额),0) from cte c2 where c1.lv<c2.lv and charindex(','+c1.区域+',',c2.qy)>0),上级区域 from cte c1
