如何基于基础表ID关联多表并按ID聚合各表对应值求和
2026-10-10
多表关联按参照ID聚合求和需求
我需要关联5张数据表，这些表与存储全量ID的基础表共享同一个参照ID。待关联的表不是每个参照ID都有对应数值，部分参照ID还可能对应多条记录，最终需要输出按参照ID分组，对各表对应数值求和的结果。
示例说明
基础表
| 参照ID | 基础数值 |
|---|---|
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | g |
| 8 | h |
表1
| 参照ID | 表1数值 |
|---|---|
| 1 | i |
| 2 | j |
| 2 | x |
| 3 | k |
| 4 | l |
表2
| 参照ID | 表2数值 |
|---|---|
| 1 | m |
| 5 | n |
| 7 | o |
| 7 | y |
| 8 | p |
表3
| 参照ID | 表3数值 |
|---|---|
| 2 | q |
| 4 | r |
| 6 | s |
| 8 | t |
| 8 | z |
期望输出结果
| 参照ID | 基础数值 | 表1数值求和 | 表2数值求和 | 表3数值求和 |
|---|---|---|---|---|
| 1 | a | i | m | |
| 2 | b | (j+x) | q | |
| 3 | c | k | ||
| 4 | d | l | r | |
| 5 | e | n | ||
| 6 | f | s | ||
| 7 | g | (o+y) | ||
| 8 | h | p | (t+z) |
已编写的实现代码
SELECT T0."STATUS", T0."DocNum" AS "ProjectNumber", T0."NAME", T0."CARDNAME" AS "Client", T0."FINISHED" AS "Project Finished", T1."PoPhAmt" AS "Project Value", T1."PhBudget" AS "Budget", (T1."PoPhAmt"-T1."PhBudget") AS "Planned Gross Profit", T1."TotalAP" AS "Ordered", SUM(T2."PaidSys") AS "Paid Downpayments(Client)", COUNT(T2."PaidSys"), SUM(T3."PaidSys") AS "Paid Invoices(Client)", COUNT(T3."PaidSys"), SUM(T4."PaidSys") AS "Creditnotes(Client)", COUNT(T4."PaidSys") FROM (OPMG T0 INNER JOIN PMG8 T1 ON T0."AbsEntry" = T1."AbsEntry") LEFT JOIN ODPI T2 ON T0."FIPROJECT" = T2."Project" LEFT JOIN OINV T3 ON T0."FIPROJECT" = T3."Project" LEFT JOIN ORIN T4 ON T0."FIPROJECT" = T4."Project" WHERE T0."FINISHED" < '100' AND T0."STATUS" <> 'N' AND T0."STATUS" <> 'P' GROUP BY T0."STATUS", T0."DocNum", T0."NAME", T0."CARDNAME", T0."FINISHED" , T1."PoPhAmt", T1."PhBudget", T1."TotalAP" ORDER BY T0."DocNum"
内容的提问来源于stack exchange，提问作者André Schwarze
相关产品推荐
相关产品推荐
