Question

我一直在寻找正确的方式从两个不同的桌子上抽取两排

我可以很容易地辨别出我想用这两个问题来总结的行数:

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   |
+-----------------+-----------------+-----------------+

前3项总数减去后3项总数。

我尝试了加入但我真的很难得到正确的结果。

如有任何建议,将不胜感激。

Answer 1

尝试此 :

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 ;

如果不符合您的需求,请尝试使用 LEEFT 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 ;

Answer 2

你只是试图做到这一点:

select (a.amt_balance_GBP  - b.amt_balance_GBP) as GBP, 
   (a.amt_balance_EUR - b.amt_balance_EUR) as EUR,
   (a.amt_balance_USD - b.amt_balance_USD) as USD
from  vui_trading_review_form_client a, vui_trading_review_form_bank b
where a.month =  201202  and a.client_id = TOTALS  and
      a.month = b.month and a.client_id = b.provider

友情链接