代码之家  ›  专栏  ›  技术社区  ›  David

如何将组中多个条件上的列求和

  •  14
  • David  · 技术社区  · 15 年前

    我想寄回一份帐户清单,上面有他们的余额、结果和收入

    Account            Transaction
    -------            -----------
    AccountID          TransactionID
    BankName           AccountID
    Locale             Amount
    Status
    

    这是我现在拥有的。有人能解释一下我错在哪里吗?

    select
        a.ACCOUNT_ID,
        a.BANK_NAME,
        a.LOCALE,
        a.STATUS,
        sum(t1.AMOUNT) as BALANCE,
        sum(t2.AMOUNT) as OUTCOME,
        sum(t3.AMOUNT) as INCOME
    from ACCOUNT a
    left join TRANSACTION t1 on t1.ACCOUNT_ID = a.ACCOUNT_ID
    left join TRANSACTION t2 on t2.ACCOUNT_ID = a.ACCOUNT_ID and t2.AMOUNT < 0
    left join TRANSACTION t3 on t3.ACCOUNT_ID = a.ACCOUNT_ID and t3.AMOUNT > 0
    group by a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]
    

    更新

    已按照下面的注释更正了t2 left join语法。

    我期望的结果是从这个问题上很明显的。对于6个帐户,sql应该返回6个帐户及其余额、收入和结果。

    我提供的sql的问题是数字错了!根据我的评论,我认为这个问题源于多次连接,这是不正确的总和金额。

    4 回复  |  直到 15 年前
        1
  •  18
  •   outis    12 年前

    因为你没告诉我们 what's going wrong (也就是说,除了描述你期望的行为之外,还要描述你得到的行为),很难说在哪里,但是有几种可能性。尼尔指出了一个问题。另一种情况是,由于您在事务表上加入了三次,因此您将事务与事务配对并获得重复。相反,只需在事务表上加入一次并更改 Amount 列。

    Select
        a.ACCOUNT_ID,
        a.BANK_NAME,
        a.LOCALE,
        a.STATUS,
        sum(t.AMOUNT) as BALANCE,
        sum((t.AMOUNT < 0) * t.AMOUNT) As OUTGOING,
        sum((t.AMOUNT > 0) * t.AMOUNT) As INCOMING
    From ACCOUNT a
    Left Join TRANSACTION t On t.ACCOUNT_ID = a.ACCOUNT_ID
    Group By a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]
    

    你可以使用 CASE 表达式作为乘法的更可读的替代:

    Select
        a.ACCOUNT_ID,
        a.BANK_NAME,
        a.LOCALE,
        a.[STATUS],
        sum(t.AMOUNT) As BALANCE,
        sum(CASE WHEN t.AMOUNT < 0 THEN t.AMOUNT ELSE 0 end) As OUTCOME,
        sum(CASE WHEN t.AMOUNT > 0 THEN t.AMOUNT ELSE 0 end) As INCOME
    From ACCOUNT a
    Left Join [TRANSACTION] t On t.ACCOUNT_ID = a.ACCOUNT_ID
    Group By a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]
    
        2
  •  6
  •   Mitch Wheat    8 年前

    你是说:

    select 
        a.ACCOUNT_ID, 
        a.BANK_NAME, 
        a.LOCALE, 
        a.STATUS, 
        sum(t1.AMOUNT) as BALANCE, 
        sum(CASE WHEN t2.AMOUNT < 0 THEN t2.Amount ELSE 0 END) as OUTCOME, 
        sum(CASE WHEN t3.AMOUNT > 0 THEN t3.Amount ELSE 0 END) as INCOME 
    from 
        ACCOUNT a 
        left join TRANSACTION t1 on t1.ACCOUNT_ID = a.ACCOUNT_ID 
        left join TRANSACTION t2 on t2.ACCOUNT_ID = a.ACCOUNT_ID
        left join TRANSACTION t3 on t3.ACCOUNT_ID = a.ACCOUNT_ID
    group by 
        a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS] 
    
        3
  •  3
  •   Thomas    15 年前

    我不知道你为什么需要多重连接。你就不能简单地做些什么吗?

    Select
        a.ACCOUNT_ID
        , a.BANK_NAME
        , a.LOCALE
        , a.STATUS
        , Sum ( t.Amount ) As Balance
        , Sum( Case When t.Amount < 0 Then Amount End ) As Outcome
        , Sum( Case When t.Amount > 0 Then Amount End ) As Income
    From ACCOUNT a
        Left Join TRANSACTION t 
            On t.ACCOUNT_ID = a.ACCOUNT_ID
    Group By a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]
    
        4
  •  1
  •   Bhargav Rao rlgjr    6 年前

    事务t2的连接应该是 在T2上 如在 on t2.ACCOUNT_ID = a.ACCOUNT_ID