我的几张表结构如下:
表adrr
Id pId azName
2 NULL 北京市
13 2 昌平区
16 13 区域1
17 13 区域2
18 2 通州区
19 18 南部
20 18 北部表cmg ,ADid对应adrr表Id
SID ADid
0176 16
0205 16
0208 17
0209 19
0001 19
0066 20
5555 20表cft,SID对应表cmg表的SID
SID tId sumNO
0176 01003028 9.69
0176 01003006 9.69
0176 01003070 16.72
0176 01003025 16.72
5555 01003021 16.72
5555 01003045 9.69我想要得到的结果是,传入adrr表ID,求和,例子如下
传入ID为:13,18得到结果是这样的:
txt sumNO
昌平区 52.82
通州区 26.41实在写不出来了,求解,万分感谢
@DBA_Huangzj @roy_88 @wmxcn2000
表adrr
Id pId azName
2 NULL 北京市
13 2 昌平区
16 13 区域1
17 13 区域2
18 2 通州区
19 18 南部
20 18 北部表cmg ,ADid对应adrr表Id
SID ADid
0176 16
0205 16
0208 17
0209 19
0001 19
0066 20
5555 20表cft,SID对应表cmg表的SID
SID tId sumNO
0176 01003028 9.69
0176 01003006 9.69
0176 01003070 16.72
0176 01003025 16.72
5555 01003021 16.72
5555 01003045 9.69我想要得到的结果是,传入adrr表ID,求和,例子如下
传入ID为:13,18得到结果是这样的:
txt sumNO
昌平区 52.82
通州区 26.41实在写不出来了,求解,万分感谢
@DBA_Huangzj @roy_88 @wmxcn2000
/* 测试数据
adrr(Id,pId,azName)AS(
SELECT 2,NULL,'北京市' UNION ALL
SELECT 13,2,'昌平区' UNION ALL
SELECT 16,13,'区域1' UNION ALL
SELECT 17,13,'区域2' UNION ALL
SELECT 18,2,'通州区' UNION ALL
SELECT 19,18,'南部' UNION ALL
SELECT 20,18,'北部'
)
,cmg(SID,ADid)AS(
SELECT 0176,16 UNION ALL
SELECT 0205,16 UNION ALL
SELECT 0208,17 UNION ALL
SELECT 0209,19 UNION ALL
SELECT 0001,19 UNION ALL
SELECT 0066,20 UNION ALL
SELECT 5555,20
)
,cft(SID,tId,sumNO)AS(
SELECT 0176,01003028,9.69 UNION ALL
SELECT 0176,01003006,9.69 UNION ALL
SELECT 0176,01003070,16.72 UNION ALL
SELECT 0176,01003025,16.72 UNION ALL
SELECT 5555,01003021,16.72 UNION ALL
SELECT 5555,01003045,9.69
), */
A AS ( -- 递归出所有下属Id
SELECT pID AS RootID,
Id
FROM adrr
WHERE pID in (13,18)
UNION ALL
SELECT A.RootID,
adrr.Id
FROM A
JOIN adrr
ON A.Id = adrr.pID
)
SELECT adrr.azName AS txt,
SUM(cft.sumNO) AS sumNO
FROM A
JOIN adrr
ON A.RootId = adrr.Id
LEFT JOIN cmg
ON A.Id = cmg.ADid
LEFT JOIN cft
ON cmg.SID = cft.SID
GROUP BY A.RootId, adrr.azName
txt sumNO
------ ------------
昌平区 52.82
通州区 26.41