请教 PostgreSQL 创建收支平衡 视图 的方法

Question

数据库中有两张表：
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请问该如何创建？谢谢！

Answer 1

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 -- 这个应该可以不用

Answer 2

谢谢答复。我运行了上面的代码，但是数据库报错：
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

Answer 3

改成CASE WHENSELECT 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
。

Answer 4

IF.... 改为case when ...then ... end 
SUM(case when type = 'r' then amount else 0 end) AS revenue_sum

Answer 5

谢谢 shine333，ACMAIN_CHM 和 trainee，将 IF 改为 CASE WHEN 就能显示出来了！效果如图：
不过有没有办法让空白的地方置零呢，这样balance就能有计算结果了。

Answer 6

解决了，用函数 COALESCE 可以将空值置零。SUM(COALESCE((CASE WHEN type = 'r' THEN amount END),0))谢谢大家帮助！

Answer 7

参见5楼
CASE WHEN .... THEN amount ELSE 0 END
少用一层函数，加快速度