如果不存在“ IN”字段，我希望“ WHERE IN”返回0

本文介绍了如果不存在“ IN”字段，我希望“ WHERE IN”返回0的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

考虑查询：

SELECT id, count(id) AS "count" FROM table WHERE id IN ( 'value1', 'value2', 'value3' ) GROUP BY id ORDER BY count ASC;

如果每个 IN 值都存在在表中，则得到这样的结果：

If each of those IN values are present in table, then I'd get a result as such:

RESULTS ------- value1 <value> value2 <value> value3 <value>

但是，如果说 value2 不是出现在表中，那么我会得到

But if, say, value2 was not present in table, then I'd get

RESULTS ------- value1 <value> value3 <value>

请注意结果中缺少 value2 。

Notice the absence on value2 in the results.

我想显示 value2 且值为 0 。

I'd like to have value2 displayed with a value of 0.

对于上下文，我有一个与订阅ID关联的Rep ID表。我想查询这个以获取最少订阅数的销售代表。问题在于此表中的代表ID并不详尽。因此，我以编程方式查询一个单独的数据源，其中包含Rep ID的详尽列表。我的想法是使用该Rep ID的详尽列表创建查询，以获得我在上面提到的所需响应。

For context, I have a table of Rep IDs associated with Subscription IDs. And I want to query this to get the Rep with the least amount of Subscriptions. The issue is that the Rep IDs in this table are not exhaustive. So, I programmatically query a separate datasource that contains the exhaustive list of Rep IDs. My idea is to somehow create a query, using that exhaustive list of Rep IDs, to get the desired response I mentioned above.

有人对此有任何想法吗？我曾看到过类似的帖子，其中解决方案是使用 COALESCE ，但是我的子查询返回了多个结果，这导致PostgreSQL崩溃。

Does anyone have any ideas for this? I've seen similar postings about this where the solution was to use COALESCE, but my subquery returns more than one result, which caused PostgreSQL to burp.

推荐答案

您可以使用 values（）子句建立可以外部连接的列表。 ：

You can build up a list that you can outer join to with a values() clause:

SELECT d.id, count(t.id) AS "count" FROM ( values ('value1'), ('value2'), ('value3') ) as data (id) left join the_table t on t.id = d.id GROUP BY d.id ORDER BY count ASC;

请注意，您必须使用 count（t.id）（对联接表中的值进行计数）以得到0的计数

Note that you must use count(t.id) (counting values from the joined table) to get the count of 0

或者，如果您不想使 from 部分中带有 values 子句：

Alternatively you can use a common table expression if you don't want to clutter the from part with the values clause:

with data (id) as ( values ('value1'), ('value2'), ('value3') ) SELECT d.id, count(t.id) AS "count" FROM data d left join the_table t on t.id = d.id GROUP BY d.id ORDER BY count ASC;