数据库中有两张表:
t_revenue 收入表,
t_payment 支出表。
t_revenue 表结构:
id date name revenue
1 2011-01-22 工资 1000
2 2011-01-22 奖金 200
3 2011-02-26 工资 2000
4 2011-02-26 奖金 500t_payment 表结构:
id date name payment
1 2011-01-01 充值 -100
2 2011-01-01 水电 -96
3 2011-02-02 充值 -200
4 2011-02-02 水电 -60
5 2011-02-26 停车 -10
现想创建一个收支平衡视图 v_balance,视图期望效果如下:date revenue_sum payment_sum balance
2011-01-01 0 -196 -196
2011-01-22 1200 0 1200
2011-02-02 0 260 -260
2011-02-26 2500 -10 2490请问该如何创建?谢谢!
t_revenue 收入表,
t_payment 支出表。
t_revenue 表结构:
id date name revenue
1 2011-01-22 工资 1000
2 2011-01-22 奖金 200
3 2011-02-26 工资 2000
4 2011-02-26 奖金 500t_payment 表结构:
id date name payment
1 2011-01-01 充值 -100
2 2011-01-01 水电 -96
3 2011-02-02 充值 -200
4 2011-02-02 水电 -60
5 2011-02-26 停车 -10
现想创建一个收支平衡视图 v_balance,视图期望效果如下:date revenue_sum payment_sum balance
2011-01-01 0 -196 -196
2011-01-22 1200 0 1200
2011-02-02 0 260 -260
2011-02-26 2500 -10 2490请问该如何创建?谢谢!
SUM(IF(type = 'r', amount, 0) AS revenue_sum,
SUM(IF(type = 'p', amount, 0) AS payment_sum,
revenue_sum + payment_sum AS balance
FROM
(
SELECT date,
revenue AS amount,
'r' AS type
FROM t_revenue
UNION ALL
SELECT date,
payment AS amount,
'p' AS type
FROM t_payment
) x
GROUP BY date
-- ORDER BY date -- 这个应该可以不用
ERROR: function if(boolean, numeric, integer) does not exist
LINE 2: SUM(IF(type = 'r', amount, 0)) AS revenue_sum,
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
********** 错误 **********ERROR: function if(boolean, numeric, integer) does not exist
SQL 状态: 42883
指导建议:No function matches the given name and argument types. You might need to add explicit type casts.
字符:22运行的代码:SELECT date,
SUM(IF(type = 'r', amount, 0)) AS revenue_sum,
SUM(IF(type = 'p', amount, 0)) AS payment_sum,
revenue_sum + payment_sum AS balance
FROM (
SELECT date,
revenue AS amount,
'r' AS type
FROM t_revenue
UNION ALL SELECT date,
payment AS amount,
'p' AS type
FROM t_payment
) x GROUP BY date
ORDER BY date
SUM(case when type = 'r' then amount end) AS revenue_sum,
SUM(case when type = 'p' then amount end) AS payment_sum,
SUM(case when type = 'r' then amount end) + SUM(case when type = 'p' then amount end) AS balance
FROM
(
SELECT date,
reven
。
SUM(case when type = 'r' then amount else 0 end) AS revenue_sum
不过有没有办法让空白的地方置零呢,这样balance就能有计算结果了。
参见5楼
CASE WHEN .... THEN amount ELSE 0 END
少用一层函数,加快速度