如何在sql server中减去两个不同表中的值 [英] How to subtract two values in sql server which are in different table

问题描述

我有两张收费详情的桌子（fee_details_memo，fee_paid_details）



table1 fee_details_memo

i am having two tables with fee details (fee_details_memo,fee_paid_details)

table1 fee_details_memo

registration_id|Fee_type       |Amount
1001           |admission_fee  |200
1001           |tution_fee     |500
1001           |transportation |500

table2 fee_paid_details

table2 fee_paid_details

registration_id|Fee_type       |Amount |Paid_On
1001           |admission_fee  |100    |jan/2013
1001           |admission_fee  |50     |feb/2013
1001           |admission_fee  |10     |feb/2013

现在我想要的是来自table1的admission_fee是200而来自table2的admission_fee的总和是160然后我想用第一个表减去第一个表的值第二个表的价值和我需要的输出值，是否可能

result-> 200-160 = 40

now what i want is from table1 the "admission_fee" is 200 and from table2 the sum of the "admission_fee" is 160 then i want to subtract the value of first table with the value of second table.and the out put value i need, is it possible
result-> 200-160=40

推荐答案

SELECT
     A.REGISTRATION_ID, A.Fee_type, A.AMOUNT, B.AMOUNT AS PAID, (A.AMOUNT - B.AMOUNT) AS DUE
FROM
    TABLE1 AS A
    CROSS APPLY (SELECT SUM(AMOUNT) AS AMOUNT FROM TABLE2 WHERE REGISTRATION_ID = '1001' AND Fee_type = 'admission_fee' GROUP BY Fee_type) AS B
WHERE
    A.REGISTRATION_ID = '1001'
    AND A.Fee_type = 'admission_fee'

SELECT m.registration_id, m.Fee_type, m.Amount - g.Paid as Owing FROM fee_details_memo m
   JOIN (SELECT p.registration_id, p.Fee_type, SUM(p.Amount) as Paid FROM fee_paid_details p
            GROUP BY p.registration_id, p.Fee_type) AS g
   ON m.registration_id= g.registration_id AND m.Fee_type = g.Fee_type

这篇关于如何在sql server中减去两个不同表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！