如果不存在“ 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屋!