我有这样一张桌子:
|users_id | request_at | status | banned |
| -------- | ---------- | -------- | ------ |
| 2 | 2013-10-01 | cancelled | No |
| 3 | 2013-10-01 | cancelled | Yes |
| 4 | 2013-10-01 | cancelled | No |
| 5 | 2013-10-02 | confirmed | No |
| 6 | 2013-10-03 | cancelled | No |
| 7 | 2013-10-03 | cancelled | No |
我想统计一下在特定日期有多少行的状态!=已确认,以及哪里被禁止=否.但在条件不匹配的情况下,我也希望值为0.
select
distinct (COUNT(users_id) OVER (PARTITION BY request_at)) AS count_ban,
request_at
from Alll
where banned='No' and status != 'confirmed'
结果:
| count_ban | request_at |
| --------- | ---------- |
| 2 | 2013-10-03 |
| 3 | 2013-10-01 |
它向我显示了正确的值,但我还希望看到行
| count_ban | request_at |
| --------- | ---------- |
| 0 | 2013-10-02 |
做这件事最简单的方法是什么?