【提问】求1Sql语句_Sql Server

【提问】求一Sql语句

本帖最后由 xupeihuagudulei 于 2014-03-12 14:59:01 编辑


有以下三张表
tableA 部门表
deptId deptName parentId
1        上海      0
2        杭州      0
3        浦东      1
4        西湖      2
...      ...     ...

tableB 商户表
accountId      accountName
1                食堂A
2                食堂B
3                食堂C
..               ..
 

tableC 消费记录表
feeId      accountId       deptId     money
1           1                1         100
2           1                2         100
3           2                3         200
4           2                4         300
5           3                3         200
6           3                4         400
7           1                1         100
...         ...              ...       ...

结果表如下：
部门     食堂A    食堂B   食堂C   ...   汇总金额
上海      200     200     200   ...   500     
杭州      100     0       400   ...   500
汇总金额  200     200     600   ...   1000

对于以上结果表有以下说明：
1、结果表部门一列只列出一级部门（部门表里parentId为0）
2、部门在商户的消费金额是一级部门下的所有子部门消费金额的和（ 如：上海这个部门的消费包含上海本身部门的消费以及浦东这个部门的消费）
3、部门层级可能无限。
4、每个部门可能在同一商户消费多次，如消费记录表第1、7两条记录。

大牛来看看！！！！！！，成分感谢！
------解决方案--------------------

引用:


if OBJECT_ID('tempdb..#cu') is not NULL
   drop table #cu

;with a1 (deptId,deptName,parentId) AS
(
select 1,'上海',0 UNION ALL
select 2,'杭州',0 UNION ALL 
select 3,'浦东',1 UNION ALL
select 5,'浦东1',3 UNION ALL
select 6,'浦东2',3 UNION ALL
select 4,'西湖',2
)
,a2 (accountId,accountName) AS 
(
SELECT 1,'食堂A' UNION ALL
SELECT 2,'食堂B' UNION ALL 
SELECT 3,'食堂C' 
) 
,a3 (feeId,accountId,deptId,money) AS
(
select 1,1,1,100 UNION ALL
select 2,1,2,100 UNION ALL
select 3,2,3,200 UNION ALL
select 4,2,4,300 UNION ALL
select 5,3,3,200 UNION ALL
select 6,3,4,400 
)
,b0 AS
(
SELECT DISTINCT deptId FROM a3
)
,b1 AS
(
	SELECT a1.deptId deptId_r,a1.deptId,parentId 
	FROM a1
	JOIN b0 ON a1.deptId=b0.deptId
	WHERE parentId>0
	UNION ALL
	SELECT b1.deptId_r,a1.deptId,a1.parentId
	FROM b1
	JOIN a1 ON b1.parentId=a1.deptId
	WHERE a1.parentId>0
)
,b2 AS
(
SELECT deptId_r,parentId FROM b1
except
SELECT deptId_r,deptId FROM b1
UNION ALL
SELECT a1.deptId,a1.deptId
FROM a1
JOIN b0 ON a1.deptId=b0.deptId
WHERE parentId=0