如何对具有多个值的多列求和 [英] How to Sum multiple Column with multiple value
问题描述
我正在寻找以下解决方案:
-
进入用户表,找到在站点上列出项目的用户.在此用户表中,没有关于拍卖的列.而是使用键将其连接到帐户表(在帐户中,此列称为用户)
-
从这些ID(列出了要拍卖物品的用户)中,我需要找到其帐户余额.这也在帐户表中.余额包含在名为operation_amount的列中.我还有另一列称为operation_type的列,它描述用户的余额是正数还是负数.例如,如果operation_type = 1,则其余额为负;而如果operation_type = 2,则其余额为正.
现在,我有另一个名为tmpinvoice
的表,其中有一个称为金额的列.这显示了用户需要向站点管理员支付多少费用.
鉴于此,我需要计算他必须支付的总金额.例如,如果用户有200美元的余额,我需要根据operation_type
检查它是负数还是正数.
所以我有一个查询,在哪里执行此操作仅用于记录
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
这是我收到的结果
http://gyazo.com/3d7e7f52ee14d21cc8c8d33b6bbc479a
是的,但这仅针对列中的1个值计算费用",如果用户有多个值怎么办
喜欢该用户:
http://gyazo.com/c3bdb29fa235044ab888dc0385bbcdbd
我需要根据该给定用户的operation_amount计算总金额,并从该总金额中删除tmpinvoice
,
我的一个朋友告诉我使用
IF(SUM(ac.operation_amount), IS NULL , 0, sum(ac.operation_amount)
,并使用+和-
两种情况加入2个时间账户(表)加入1次+,2次--p
但我不知道它的外观如何:)
在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
上查看演示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)
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.
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.
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
.
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
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 ,
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 -
join 1 time for + , 2 time for -
but I can't figure out how will looks :)
Use CASE expression within the SUM function.
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
这篇关于如何对具有多个值的多列求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!