总结两个总结语句

Question

DECLARE @aracct VARCHAR(12) 
    SET @aracct = '49920813' 
DECLARE @item_no VARCHAR(12) 
    SET @item_no = '2319' 
SELECT tran_id, * FROM transactions 
    WHERE account = @aracct AND item_no = @item_no 
/*-----------Use if removing certain items from a check-----------*/ 
CREATE TABLE itemtable (items VARCHAR(6), account VARCHAR(12)) 
    INSERT INTO itemtable VALUES ('2299', @aracct) 
    INSERT INTO itemtable VALUES ('2300', @aracct) 
    --INSERT INTO @itemtable VALUES ('') 
DECLARE @tran_id_pmt VARCHAR(6) 
    SET @tran_id_pmt = '209413' 

SELECT I1.result - I2.result AS 'final result' 
FROM (SELECT SUM(amt_paid) AS result 
     FROM transactions 
     WHERE account = @aracct AND item_no = @item_no) AS I1 
JOIN (SELECT SUM(amt_paid) AS result 
     FROM transactions 
     WHERE account = @aracct AND item_no = (SELECT items FROM itemtable)) AS I2 
    ON (I1.account = I2.account) 

试图获得总和2选择语句，然后总结这2个语句，但我不断收到错误。有任何想法吗？

Answer 1

您需要包括account你的子查询，所以你可以在ON条件

SELECT I1.result - I2.result AS 'final result' 
FROM (SELECT account, SUM(amt_paid) AS result 
      ^^^^^^^ 
     FROM transactions 
     WHERE account = @aracct AND item_no = @item_no 
     GROUP BY account) AS I1 
     ^^^^^^^^^^^^^^^^ 
JOIN (SELECT account, SUM(amt_paid) AS result 
     FROM transactions 
     WHERE account = @aracct AND item_no = (SELECT items FROM itemtable) 
     GROUP BY account) AS I2 
    ON (I1.account = I2.account) 
     ^^^^^^^^ 

使用它，你也可以用一个有条件的聚合解决它。

SELECT SUM(CASE WHEN item_no = @item_no THEN amt_paid 
             ELSE 0 
      END) 
     - 
     SUM(CASE WHEN item_no IN (SELECT items FROM itemtable) THEN amt_paid 
                   ELSE 0 
      END) AS 'final result'   
FROM Transactions 
WHERE account = @aracct