postgresql - Postgresql如何在同一个表中使用联合进行两个选择？

Question

我有一张桌子：

ID	created_at	作者
1	02.02.2020	经理
2	02.02.2020	客户
3	02.02.2020	经理

我想得到如下结果，并且日期排序：

日期	经理	客户
02.02.2020	2	1
03.02.2020	5	3

我试过了：

SELECT created_at::date, count(id) AS manager FROM orders where author = 'manager' GROUP BY created_at::date
union
SELECT created_at::date, count(id) AS client FROM orders where author = 'client' GROUP BY created_at::date

我得到了：

日期	经理
03.02.2020	2
02.02.2020	5

标签： postgresqlgroup-by

Answer 1

您不需要联合，这可以使用过滤聚合在单个查询中完成：

select created_at::date, 
       count(*) filter (where author = 'manager') as managers,
       count(*) filter (where author = 'client') as clients
from orders
group by created_at::date
order by created_at::date