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

问题描述

我有SELECT：

  SELECT c FROM（
 SELECTcandidate_idas id，count ）as c 
 FROMApplicaions
 GROUP BYcandidate_id
）as s WHERE id = _SOME_ID_; 
  

但是这只会返回一个值 count& 0 。如果 count = 0 它不返回任何内容。对于没有任何申请的候选人，如何获得 0 ？

候选人。

如果候选人没有申请或不存在，我需要获得0。

EDIT

现在：

  SELECT COALESCE（SELECT count（candidate_id）as c 
 FROMApplicaionsWHEREcandidate_id= _ SOME_ID_ 
 GROUP BYcandidate_id），0）; 
  

它工作得很好。但是它可以写​​得更简单还是这是最好的解决方案？

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

本质上你需要告诉SQL如何处理 NULL 。即 NULL 返回 0 。

I have SELECT:

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_;

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?

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

EDIT

I have now:

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?

解决方案

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

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

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