如何在sql(toad)中添加两个基于年的金额总和的表？

ohtdti5x 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(423)

关闭。这个问题需要细节或清晰。它目前不接受答案。
**想改进这个问题吗？**通过编辑这个帖子来添加细节并澄清问题。

10个月前关门了。
改进这个问题
第一张table：

SELECT A.BU,A.PROJECT,SUM(A.BUDGET_AMOUNT),SUBSTR(A.BUDGET_PERIOD,3,2)
FROM BUDGET_SUMMARY A
GROUP BY A.BU,A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)

第二张table：

SELECT B.BU,A.PROJECT,SUM(B.ACTUAL_AMOUNT),SUBSTR(B.ACTUAL_PERIOD,3,2)
FROM ACTUAL_SUMMARY B
GROUP BY B.BU,B.PROJECT,SUBSTR(B.ACTUAL_PERIOD,3,2)

现在，我想以

BU|PROJECT|SUM(BUDGET_AMOUNT)|SUBSTR(A.BUDGET_PERIOD,3,2)|SUM(B.ACTUAL_AMOUNT)|SUBSTR(B.ACTUAL_PERIOD,3,2)

sql mysql Join Database toad

来源：https://stackoverflow.com/questions/62859247/how-to-add-two-tables-having-sum-of-amounts-on-the-basis-of-year-in-sqltoad

2条答案

按热度按时间

cl25kdpy1#

SELECT A.BU,A.PROJECT, SUM(A.BUDGET_AMOUNT), SUBSTR(A.BUDGET_PERIOD,3,2), SUM(B.ACTUAL_AMOUNT)
FROM BUDGET_SUMMARY A
GROUP BY A.BU,A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)

两个表的or值不相同，可以使用join（内部join表示匹配值）

SELECT A.BU,A.PROJECT,SUM(A.BUDGET_AMOUNT),SUBSTR(A.BUDGET_PERIOD,3,2), T.SUM_BUDGET_AMOUNT, T.SUB_ACTUAL_PERIOD
    FROM BUDGET_SUMMARY A
    INNER JOIN  (
        SELECT B.BU,A.PROJECT,SUM(B.ACTUAL_AMOUNT) sum_BUDGET_AMOUNT,SUBSTR(B.ACTUAL_PERIOD,3,2) SUB_ACTUAL_PERIOD
        FROM ACTUAL_SUMMARY B
        GROUP BY B.BU,B.PROJECT,SUBSTR(B.ACTUAL_PERIOD,3,2)
        ) T  ON T.BU = A.BY 
            AND T.PROJECT = A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)
                AND T.SUB_ACTUAL_PERIOD = ,SUBSTR(A.BUDGET_PERIOD,3,2)
    GROUP BY A.BU,A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)

如果你有一个匹配的左连接

SELECT A.BU,A.PROJECT,SUM(A.BUDGET_AMOUNT),SUBSTR(A.BUDGET_PERIOD,3,2), T.SUM_BUDGET_AMOUNT, T.SUB_ACTUAL_PERIOD
    FROM BUDGET_SUMMARY A
    LEFT  JOIN  (
        SELECT B.BU,A.PROJECT,SUM(B.ACTUAL_AMOUNT) sum_BUDGET_AMOUNT,SUBSTR(B.ACTUAL_PERIOD,3,2) SUB_ACTUAL_PERIOD
        FROM ACTUAL_SUMMARY B
        GROUP BY B.BU,B.PROJECT,SUBSTR(B.ACTUAL_PERIOD,3,2)
        ) T  ON T.BU = A.BY 
            AND T.PROJECT = A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)
                AND T.SUB_ACTUAL_PERIOD = ,SUBSTR(A.BUDGET_PERIOD,3,2)
    GROUP BY A.BU,A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)

展开查看全部

赞(0）回复(0）举报 2021-07-26

tez616oj2#

只是猜测一下，你的描述很难阅读，也不可执行。命名子查询应该有助于获得所需的结果（假设“project”可以用作连接列）

with budget as
(
SELECT A.BU,A.PROJECT,SUM(A.BUDGET_AMOUNT) sum_amount,SUBSTR(A.BUDGET_PERIOD,3,2) m_period FROM BUDGET_SUMMARY A GROUP BY A.BU,A.PROJECT,SUBSTR(A.BUDGET_PERIOD,3,2)
), actual as
(
SELECT B.BU,B.PROJECT,SUM(B.ACTUAL_AMOUNT) sum_amount,SUBSTR(B.ACTUAL_PERIOD,3,2) m_period FROM ACTUAL_SUMMARY B GROUP BY B.BU,B.PROJECT,SUBSTR(B.ACTUAL_PERIOD,3,2)
)
select  budget.BU,budget.PROJECT,budget.sum_amount,budget.m_period,actual.sum_amount,actual.m_period
from budget
join actual on budget.project=actual.project

（可能需要左连接）

赞(0）回复(0）举报 2021-07-26

我来回答

如何在sql(toad)中添加两个基于年的金额总和的表？

2条答案

相关问题

热门标签

最新问答