如何使用多个值对多个列求和

I am looking for a solution to the following:

我正在寻找以下解决方案:

Go in to the users table and find a user who has listed items on the site. In this users table, there is no column about auctions. Instead, it is connected to an accounts table with a key (in accounts, this column is called user)

转到users表,找到在站点上列出项目的用户。在此用户表中,没有关于拍卖的列。相反,它连接到带有密钥的帐户表(在帐户中,此列称为用户)
From these IDs (users which have listed items for auction), I need to find their account balance. This is also in the accounts table. The balance is contained in a column called operation_amount. I also have another column called operation_type which describes whether a user has a positive or negative balance. For example, if operation_type = 1, he has a negative balance, while if operation_type = 2, he has a positive balance.

从这些ID(已列出拍卖物品的用户)中,我需要找到他们的帐户余额。这也在帐户表中。余额包含在名为operation_amount的列中。我还有另一个名为operation_type的列,它描述用户是否具有正余额或负余额。例如,如果operation_type = 1,则他具有负余额,而如果operation_type = 2,则他具有正余额。

Now I have another table called tmpinvoice where there is a column called amount. This shows how much in fees a user needs to pay to the site administrators.

现在我有另一个名为tmpinvoice的表,其中有一个名为amount的列。这显示了用户需要向网站管理员支付多少费用。

Given this, I need to calculate how much he must pay in total. For example, if a user has a $200 balance, I need to check whether it's negative or positive based on the operation_type.

鉴于此,我需要计算他必须支付多少钱。例如,如果用户有200美元的余额,我需要根据operation_type检查它是负数还是正数。

So I have query where is Do this only for record

所以我有查询在哪里这样做只是为了记录

SELECT u.id AS id_user, u.nick,
  CASE ac.operation_type WHEN 1 THEN ac.operation_amount - tm.amount
                         WHEN 2 THEN ac.operation_amount + tm.amount
                                ELSE 'N/A' END AS `fee`                         
FROM auctionbg_search.accounts AS ac    
    LEFT JOIN auctionbg_search.users AS u ON TRUE
        AND u.id = ac.user
    LEFT JOIN auctionbg_search.auctions AS a ON TRUE
        AND a.id = ac.auction
    LEFT JOIN auctionbg_search.tmpinvoice AS tm  ON TRUE    
WHERE TRUE
  AND tm.amount = ac.operation_amount

Here what result I'm receiving

这是我收到的结果

http://gyazo.com/3d7e7f52ee14d21cc8c8d33b6bbc479a

Yes but this calculate 'fee' only for 1 value in column , what if user have multiple values

是的,但这只计算列中的1个值的“费用”,如果用户有多个值,该怎么办

like this user :

喜欢这个用户:

http://gyazo.com/c3bdb29fa235044ab888dc0385bbcdbd

I need calculate total amount from operation_amount of that given user and remove tmpinvoice from that total amount ,

我需要从给定用户的operation_amount计算总金额,并从该总金额中删除tmpinvoice,

A friend of mine told me to use

我的一个朋友告诉我使用

IF(SUM(ac.operation_amount), IS NULL , 0, sum(ac.operation_amount)

and to join 2 time accounts(table) for both case with + and -

和+和 - 加入2个时间账户(表)的情况

join 1 time for + , 2 time for -

加入1次+,2次 -

but I can't figure out how will looks :)

但我无法弄清楚会怎么样:)

1 个解决方案

#1

Use CASE expression within the SUM function.

在SUM函数中使用CASE表达式。

   SELECT u.id AS id_user, u.nick,
     SUM(CASE ac.operation_type WHEN 1 THEN ac.operation_amount - tm.amount У
                                WHEN 2 THEN ac.operation_amount + tm.amount
                                       ELSE 'N/A' END) AS `fee`
    FROM auctionbg_search.accounts AS ac
      LEFT JOIN auctionbg_search.users AS u ON TRUE AND u.id = ac.user 
      LEFT JOIN auctionbg_search.auctions AS a ON TRUE AND a.id = ac.auction
      LEFT JOIN auctionbg_search.tmpinvoice AS tm  ON TRUE  
    WHERE TRUE AND tm.amount = ac.operation_amount
    GROUP BY u.id, u.nick

See demo on SQLFiddle

请参阅SQLFiddle上的演示

#1