SQL 从没有行的计数中获取 0 值

Get 0 value from a count with no rows

提问by Miko Kronn

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 = 0it returns nothing. How can I get 0for a "Candidate" that doesn't have any application?

但这仅返回一个值 if count > 0。如果count = 0它什么都不返回。我如何获得0没有任何申请的“候选人”？

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

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

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?

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

采纳答案by diagonalbatman

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

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

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

本质上，您需要告诉 SQL 如何处理NULLsie 当NULLreturn 0。

回答by Matthew

You can't.

你不能。

If your candidate has no applications, then you have no way to read their candidate_idvalue

如果你的候选人没有申请，那么你就无法解读他们的candidate_id价值

How would you have knowledge that a candidate exists without them being in the Applicationstable?

如果候选人不在Applications表中，您如何知道候选人存在？

In your example code, there is no candidateand therfore you couldn't possible say that a specific candidate had zero applications. By that logic there are an infinite number of candidates having zero applications.

在您的示例代码中，没有候选人，因此您不能说特定候选人的申请为零。按照这个逻辑，有无数个申请为零的候选人。

You will need a table of candidates in order to derive this information... unless your intention is to presume a candidate exists because you're asking for it by ID?

您将需要一个候选人表来获取此信息……除非您的意图是假设存在候选人，因为您是通过 ID 要求的？

EDIT

编辑

Now that you have a Candidatestable you can do this:

现在你有了一张Candidates桌子，你可以这样做：

SELECT c.ID, (SELECT COUNT(a.*) FROM Applications a WHERE a.candidate_id = c.ID) 
FROM Candidate c
WHERE ....

回答by Erwin Brandstetter

^{I was made aware of this question by the author of this related onewho was led to believe his problem could not be solved after reading here. Well, it can.}

^{这个相关问题的作者让我意识到了这个问题，他认为在阅读这里后他的问题无法解决。嗯，它可以。}

This answer is almost two years old, but the final questions were still pending.

这个答案已经快两年了，但最后的问题仍然悬而未决。

Query

询问

Is it possible to write it simpler or is this the best solution?

是否可以写得更简单，或者这是最好的解决方案？

To test for a single ID, the query you found is good. You can simplify:

要测试单个 ID，您找到的查询很好。您可以简化：

SELECT coalesce((SELECT count(candidate_id)
FROM   "Applications" WHERE candidate_id = _SOME_ID_), 0) AS c;

• The WHEREcondition limits to a single candidate_idand there is a single aggregate function in the SELECTlist. GROUP BY candidate_idwas redundant.

• The column alias was swallowed by COALESCE(). If you want to name the resulting column move the alias to the end.

• You don't need double quotes for a regular lower case identifier.

• 的WHERE条件限制了单一的candidate_id并有在一个单一的聚合函数SELECT列表。GROUP BY candidate_id是多余的。

• 列别名被COALESCE(). 如果要命名结果列，请将别名移动到末尾。

• 常规小写标识符不需要双引号。

Another, cleaner (IMHO) form would be to use LEFT JOIN:

另一种更清洁（恕我直言）的形式是使用LEFT JOIN：

SELECT count(a.candidate_id) AS c
FROM  (SELECT _SOME_ID_ AS candidate_id) x
LEFT   JOIN "Applicaions" a USING (candidate_id)

This works nicely for multiple IDs, too:

这也适用于多个 ID：

WITH x(candidate_id) AS (
   VALUES
     (123::bigint)
    ,(345)
    ,(789)
   )
SELECT x.candidate_id, count(a.candidate_id) AS c
FROM   x
LEFT   JOIN "Applicaions" a USING (candidate_id)
GROUP  BY x.candidate_id;

• LEFT JOINis typically faster for a long list than multiple WHEREclauses or an INexpression.

• LEFT JOIN对于长列表，通常比多个WHERE子句或IN表达式更快。

Or, for all rows in your table"Candidates":

或者，对于表“Candidates”中的所有行：

SELECT x.candidate_id, count(a.candidate_id) AS c
FROM   "Candidates" x
LEFT   JOIN "Applications" a USING (candidate_id)
GROUP  BY x.candidate_id;

Indexes

索引

Should I create any indexes?

我应该创建任何索引吗？

If read performance is important and the table holds more than just a couple of rows you definitelyneed an index of the form:

如果读取性能很重要并且表包含的不仅仅是几行，那么您肯定需要以下形式的索引：

CREATE INDEX foo_idx ON "Applications" (candidate_id);

Since this seems to be a foreign key column referencing "Candidates".candidate_id, you should most probably have that to begin with.

由于这似乎是一个外键列引用"Candidates".candidate_id，因此您很可能应该首先使用它。

回答by farfromhome

Perhaps:

也许：

SELECT CASE c WHEN NULL THEN 0 ELSE c END
    FROM (
    SELECT "candidate_id" as id, count("candidate_id") as c
    FROM "Applicaions"
    GROUP BY "candidate_id"
) as s WHERE id= _SOME_ID_;

assuming that the 'nothing' is really NULL

假设“无”是真的 NULL

回答by Marek Tuchalski

Can't you use this statement:

你不能用这个语句：

SELECT count("candidate_id") as c
FROM "Applicaions" WHERE "candidate_id"=_SOME_ID_
GROUP BY "candidate_id"

It should return count() and you don't need subquery.

它应该返回 count() 并且您不需要子查询。

EDIT: Matthew PK is correct and Andy Paton has better answer ;)

编辑：Matthew PK 是正确的，Andy Paton 有更好的答案；)

回答by wllmsaccnt

You could try something like the following (assuming you have a candidate table and my assumption of table/column names are correct).

您可以尝试以下操作（假设您有一个候选表并且我对表/列名称的假设是正确的）。

SELECT c FROM (
 SELECT "Candidate"."candidate_id" as id, 
 count("Applications"."candidate_id") as c
 FROM "Candidate" LEFT JOIN "Applications" 
 ON "Applications"."candidate_id"="Candidate"."id"     
GROUP BY "Candidate"."candidate_id" ) as s WHERE id= _SOME_ID_;