如果不存在“ IN”字段，我希望“ WHERE IN”返回0 [英] I want `WHERE IN` to return 0 if an `IN` field does not exist

问题描述

考虑查询：

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;

这篇关于如果不存在“ IN”字段，我希望“ WHERE IN”返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！