我有一张桌子：
| 身份证 | 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 |
您不需要联合，这可以使用过滤聚合在单个查询中完成：
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
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句