从没有行的计数中获取 0 值 [英] Get 0 value from a count with no rows

问题描述

我有选择:

SELECT c FROM (
    SELECT "candidate_id" as id, count("candidate_id") as c
    FROM "Applicaions"
    GROUP BY "candidate_id"
) as s WHERE id= _SOME_ID_;

但是这仅在 count > 时返回一个值.0.如果 count = 0 它什么都不返回.如何为候选人"获得 0没有任何应用程序?

But this only returns a value if count > 0. If count = 0 it returns nothing. How can I get 0 for a "Candidate" that doesn't have any application?

有表候选人".
如果候选人没有申请或不存在，我需要得到 0.

There is table "Candidates".
I need to get 0 if candidate has no applications or does not exist.

我现在有:

SELECT COALESCE ((SELECT count("candidate_id") as c
FROM "Applicaions" WHERE "candidate_id"=_SOME_ID_
GROUP BY "candidate_id"), 0);

它完美地工作.但是有没有可能写得更简单，或者这是最好的解决方案?我应该创建任何索引吗?

It works perfectly. But is it possible to write it simpler or is this the best solution? Should I create any indexes?

推荐答案

需要使用 PostgreSQL 中的 COALESCE 函数 http://developer.postgresql.org/pgdocs/postgres/functions-conditional.html

You need to use the COALESCE function in PostgreSQL http://developer.postgresql.org/pgdocs/postgres/functions-conditional.html

本质上，您需要告诉 SQL 如何处理 NULLs.即当 NULL 返回 0.

Essentially you need to tell SQL how to handle NULLs. i.e. When NULL return 0.

这篇关于从没有行的计数中获取 0 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！