sql - Postgresql 没有返回任何内容

Question

我有这个 SQL 查询:

select task_id, count(status) as not_paided 
        from some_table
        where status = 'not_paid' and task_id = 34
        group by task_id

如果没有 not_paid 行，它应该返回 34/0 (task_id/not_paided)，但它什么也不返回。我不知道该怎么做，已经尝试过 case 和 coalesce。

Answer 1

34/0 是不可能的，因为这个 where 子句有零行。如果你真的需要它，你需要一个子查询。像这样的 Somthine:

select 
    main.task_id, 
    (select count(sub.status) 
     from some_table as sub 
     where sub.status = 'not_paid' 
     and sub.tast_id = main.task_id) as not_paided
from some_table as main
where main.task_id = 34

编辑:

另一种方法是 case when 使用简单的 sum

select 
    task_id, 
    sum(case when status = 'not_paid' then 1 else 0 end) as not_paided
from some_table
where task_id = 34
group by task_id