我一直在尝试寻找正确的方法来求和来自2个不同表的2行。

我可以使用以下两个查询轻松确定要累加的行:

select *
from   vui_trading_review_form_client
where  month = '201202' and client_id='TOTALS';

+--------+------------+-----------+-----------------+-----------------+-----------------+----------------+----------+
| month  | dt_end     | client_id | amt_balance_GBP | amt_balance_EUR | amt_balance_USD | dt_working_day | order_no |
+--------+------------+-----------+-----------------+-----------------+-----------------+----------------+----------+
| 201202 | 2012-02-29 | TOTALS    |            2    |             3   |               4 | 2012-02-29     |        2 |
+--------+------------+-----------+-----------------+-----------------+-----------------+----------------+----------+


select *
from   vui_trading_review_form_bank
where  month = '201202' and provider='TOTALS';

+--------+------------+----------+-----------------+-----------------+-----------------+----------+
| month  | dt_end     | provider | amt_balance_GBP | amt_balance_EUR | amt_balance_USD | order_no |
+--------+------------+----------+-----------------+-----------------+-----------------+----------+
| 201202 | 2012-02-29 | TOTALS   |              1  |               1  |              1 |        3 |
+--------+------------+----------+-----------------+-----------------+-----------------+----------+


我要实现的是一张如下表

+-----------------+-----------------+-----------------+
| amt_balance_GBP | amt_balance_EUR | amt_balance_USD |
+-----------------+-----------------+-----------------+
| 1               |          2      |             3   |
+-----------------+-----------------+-----------------+


前三个总数减去后三个总数。

我已经尝试过加入,但我确实在努力获得正确的结果。

任何建议将不胜感激。

最佳答案

尝试这个:

SELECT  (a.amt_balance_GBP - b.amt_balance_GBP) Total_GBP,
        (a.amt_balance_EUR - b.amt_balance_EUR) Total_EUR,
        (a.amt_balance_USD - b.amt_balance_USD) Total_USD
FROM vui_trading_review_form_client a INNER JOIN vui_trading_review_form_bank b
        on (a.Client_ID = b. provider) AND
           (a.Month = b.Month)
WHERE a.month = '201202' and b.provider='TOTALS';


或者如果它不符合您的需求,请尝试使用LEFT JOIN

SELECT  (a.amt_balance_GBP - COALESCE(b.amt_balance_GBP,0)) Total_GBP,
        (a.amt_balance_EUR - COALESCE(b.amt_balance_EUR,0)) Total_EUR,
        (a.amt_balance_USD - COALESCE(b.amt_balance_USD,0)) Total_USD
FROM vui_trading_review_form_client a LEFT JOIN vui_trading_review_form_bank b
        on (a.Client_ID = b. provider) AND
           (a.Month = b.Month)
WHERE a.month = '201202' and b.provider='TOTALS';

09-30 17:08
查看更多