有表user_log
id user_id flag amount 1 1 1 12000.00 2 1 0 1000.00 3 1 0 560.00 4 1 0 2339.00 5 1 1 798.00 6 1 0 1580.00
flag: 1: 收入 0: 支出
怎么样写SQL语句查询得到
user_id 收入 支出 总计 1 19980.00 5497.00 25459.00
解决方案
15
select user_id,
sum(flag*amount) as 收入,sum((1-flag)*amount) as 支出,sum(amount) as 总计
from user_log
sum(flag*amount) as 收入,sum((1-flag)*amount) as 支出,sum(amount) as 总计
from user_log
5
SELECT user_id,
SUM(CASE flag WHEN 1 THEN amount ELSE 0 END) 收入 ,
SUM(CASE flag WHEN 0 THEN amount ELSE 0 END) 支出 ,
SUM(amount) 总金额
FROM user_log
GROUP BY user_id
SUM(CASE flag WHEN 1 THEN amount ELSE 0 END) 收入 ,
SUM(CASE flag WHEN 0 THEN amount ELSE 0 END) 支出 ,
SUM(amount) 总金额
FROM user_log
GROUP BY user_id
