WHERE 子句中的 Postgres 数组查找

发布于 2024-10-28 08:55:56 字数 319 浏览 2 评论 0原文

我有一个查询：

SELECT bar, (SELECT name FROM names WHERE value = bar) as name
FROM foobar WHERE foo = 1 and bar = ANY (1,2,3)

我的问题是，当表 foobar 中没有包含 bar = 3 的行（或请求的任何其他值）时，不会返回任何行酒吧的价值。

我希望我的查询返回一行 [bar, NULL] ，但无法想出解决此问题的方法。

这可能吗？

原文

I have a query:

SELECT bar, (SELECT name FROM names WHERE value = bar) as name
FROM foobar WHERE foo = 1 and bar = ANY (1,2,3)

My problem is, when there is no row containing bar = 3 (or whatever other value is requested) in table foobar, no rows are returned for that value of bar.

I'd like my query to return a row of [bar, NULL] instead, but can't think up a way to approach this.

Is this even possible?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

他不在意 2024-11-04 08:55:56

也许类似这种方法就是您所追求的：

testbed：

create view names as 
select 1 as value, 'Adam' as name union all select 2, 'Beth';

create view foobar as 
select 1 as foo, 1 as bar union all select 1, 2;

原始方法：

select bar, (select name from names where value = bar) as name 
from foobar 
where foo = 1 and bar = any (array[1, 2, 3]);

 bar | name
-----+------
   1 | Adam
   2 | Beth
(2 rows)

替代方法：

with w as (select unnest(array[1, 2, 3]) as bar)
select bar, (select name from names where value = bar) as name
from w left outer join foobar using(bar);

 bar | name
-----+------
   1 | Adam
   2 | Beth
   3 |
(3 rows)

如果您使用的是8.3或之前的版本，则没有内置的unnest功能，但您可以推出自己的（效率不高）替换：

create or replace function unnest(anyarray) returns setof anyelement as $
  select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) i;
$ language 'sql' immutable;

Perhaps something like this approach is what you are after:

testbed:

create view names as 
select 1 as value, 'Adam' as name union all select 2, 'Beth';

create view foobar as 
select 1 as foo, 1 as bar union all select 1, 2;

original method:

select bar, (select name from names where value = bar) as name 
from foobar 
where foo = 1 and bar = any (array[1, 2, 3]);

 bar | name
-----+------
   1 | Adam
   2 | Beth
(2 rows)

alternative method:

with w as (select unnest(array[1, 2, 3]) as bar)
select bar, (select name from names where value = bar) as name
from w left outer join foobar using(bar);

 bar | name
-----+------
   1 | Adam
   2 | Beth
   3 |
(3 rows)

If you are on 8.3 or before, there is no built-in unnest function, but you can roll your own (not very efficient) replacement:

create or replace function unnest(anyarray) returns setof anyelement as $
  select $1[i] from generate_series(array_lower($1,1), array_upper($1,1)) i;
$ language 'sql' immutable;

回复收藏 0 原文

新雨望断虹 2024-11-04 08:55:56

SELECT bar, name
FROM foobar
INNER JOIN names ON foobar.bar = names.value
WHERE foo = 1 and bar = ANY (1,2,3)

请尝试该查询。

SELECT bar, name
FROM foobar
INNER JOIN names ON foobar.bar = names.value
WHERE foo = 1 and bar = ANY (1,2,3)

Try that query instead.

回复收藏 0 原文

淡淡绿茶香 2024-11-04 08:55:56

SELECT  vals.bar, name
FROM    (
        SELECT  *
        FROM    unnest([1, 2, 3]) AS bar
        ) vals
LEFT JOIN
        foobar
ON      foobar.foo = 1
        AND foobar.bar = vals.bar
LEFT JOIN
        names
ON      names.value = vals.bar

SELECT  vals.bar, name
FROM    (
        SELECT  *
        FROM    unnest([1, 2, 3]) AS bar
        ) vals
LEFT JOIN
        foobar
ON      foobar.foo = 1
        AND foobar.bar = vals.bar
LEFT JOIN
        names
ON      names.value = vals.bar

回复收藏 0 原文

~没有更多了~