时间戳

首页 > 解决方案 > 如何将多张表与一张表的基本ID组合起来

sql - 如何将多张表与一张表的基本ID组合起来

问题描述

我想组合 5 个多个表，它们具有与包含所有 ID 的基本表相同的参考 ID。“连接”表不包含每个引用的值，但有时它们具有一个引用的多个值。输出应该是 ID 的每个值的总和。

例子：

基本表：

参考 基本值
1 一个
2 b
3 C
4 d
5 e
6 F
7 G
8 H

表格1：

参考 T1.值
1 一世
2 Ĵ
2 X
3 ķ
4 l

表 2

参考 T2.值
1 米
5 n
7 ○
7 是的
8 p

表3

参考 T3.价值
2 q
4 r
6 s
8 吨
8 z

结果应该是输出：

参考 基本值 总和（T1.值） 总和（T2.值） 总和（T3.值）
1 一个 一世 米
2 b (j+x) q
3 C ķ
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"

标签： sqlhana

解决方案

使用以下查询STRING_AGG函数和 group byBasic_Table.Reference

SELECT 
      T.Reference,
      MAX(T.Basic_Value) AS Basic_Value,
      SUM(T1.T1_Value) AS T1_Value,
      SUM(T2.T2_Value) AS T2_Value,
      SUM(T3.T3_Value) AS T3_Value
FROM BASIC_TABLE T
LEFT JOIN TABLE_1  T1 ON T.Reference=T1.Reference
LEFT JOIN TABLE_2  T2 ON T.Reference=T2.Reference
LEFT JOIN TABLE_3  T3 ON T.Reference=T3.Reference
GROUP BY T.Reference

推荐阅读