具有筛选列的 PostgreSQL View

Question

我有一张这样的 table :

date_added        owner        action
01-02-2016        1            note
04-02-2016        1            call
04-02-2016        1            call
05-02-2016        1            note
05-02-2016        1            meeting
06-02-2016        1            meeting
06-02-2016        1            note
06-02-2016        1            cal
06-02-2016        1            note
10-02-2016        1            call
10-02-2016        1            note
10-02-2016        1            meeting

我需要这样的 View :

date_added        owner        note        call        meeting
01-02-2016        1            1           0           0
04-02-2016        1            0           2           0
05-02-2016        1            0           0           1
06-02-2016        1            2           1           1
10-02-2016        1            1           1           1

我如何创建一个像这样的列

WHERE action LIKE 'note'

?

Answer 1

您可以使用CASE 表达式。

查询

select date_added, owner,
sum(case action when 'note' then 1 else 0 end) note,
sum(case action when 'call' then 1 else 0 end) call,
sum(case action when 'meeting' then 1 else 0 end) meeting
from your_table_name
group by date_added, owner;

Find demo here