时间戳

首页 > 解决方案 > 在多个表中查询总和

mysql - 在多个表中查询总和

问题描述

你好吗 ？我有mysql数据库。我试图使查询有点复杂。我将写下我到目前为止所做的事情。

查询 NO1

SELECT BONOD_AMOUNT, BONOD_DETAILS_ID
FROM  `BONOD_DETAILS` 
WHERE BONOD_ID
IN (
SELECT BONOD_ID
FROM BONOD_TABLE
WHERE USER_ID =16
)

结果是：

    BONOD_AMOUNT    BONOD_DETAILS_ID
    10000           3
    1520            4

在此处输入图像描述

查询 2

SELECT SUM( AMOUNT ) THE_SUM, BONOD_DETAILS_ID
FROM EXPENSES_TABLE
WHERE BONOD_DETAILS_ID
IN (
SELECT BONOD_DETAILS_ID
FROM BONOD_DETAILS
WHERE USER_ID =16
AND BONOD_ENABLE =1
) GROUP BY BONOD_DETAILS_ID
UNION ALL
SELECT 0 AS THE_SUM, BONOD_DETAILS_ID
FROM BONOD_DETAILS
WHERE BONOD_ID
IN (
SELECT BONOD_ID
FROM BONOD_TABLE
WHERE USER_ID =16
)
AND BONOD_DETAILS_ID NOT 
IN (SELECT BONOD_DETAILS_ID
FROM EXPENSES_TABLE
)

结果是：

 THE_SUM    BONOD_DETAILS_ID
 270        3
 0          4

在此处输入图像描述

所以，我想从 #Query1 中的 BONOD_AMOUNT 和 #Query2 中的 BONOD_AMOUNT 进行减法查询

结果可以是：

 THE_SUB    BONOD_DETAILS_ID
 9730       3
 1520       4

标签： mysqlsql

解决方案

最简单的方法是在 BONOD_DETAILS_ID 上加入这两个并减去第一列：

SELECT
    sub1.BONOD_AMOUNT - sub2.THE_SUM AS THE_SUB, sub1.BONOD_DETAILS_ID
FROM
    (
        SELECT BONOD_AMOUNT, BONOD_DETAILS_ID
        FROM  `BONOD_DETAILS` 
        WHERE BONOD_ID
        IN (
        SELECT BONOD_ID
        FROM BONOD_TABLE
        WHERE USER_ID =16
        )
    )sub1
    LEFT OUTER JOIN 
    (
        SELECT SUM( AMOUNT ) THE_SUM, BONOD_DETAILS_ID
        FROM EXPENSES_TABLE
        WHERE BONOD_DETAILS_ID
        IN (
        SELECT BONOD_DETAILS_ID
        FROM BONOD_DETAILS
        WHERE USER_ID =16
        AND BONOD_ENABLE =1
        ) GROUP BY BONOD_DETAILS_ID
        UNION ALL
        SELECT 0 AS THE_SUM, BONOD_DETAILS_ID
        FROM BONOD_DETAILS
        WHERE BONOD_ID
        IN (
        SELECT BONOD_ID
        FROM BONOD_TABLE
        WHERE USER_ID =16
        )
        AND BONOD_DETAILS_ID NOT 
        IN (SELECT BONOD_DETAILS_ID
        FROM EXPENSES_TABLE
        )
    ) sub2 ON sub1.BONOD_DETAILS_ID = sub2.BONOD_DETAILS_ID

我想这可以简化，但是由于我看不到您的数据并且不知道您的表格之间的关系，所以我不能肯定地说。我可以说这会得到你正在寻找的结果。

推荐阅读