postgresql - 如何找出哪些单词与 IN 条件不匹配？

发布于 2024-10-12 04:37:08 字数 315 浏览 2 评论 0原文

我有一个这样的查询（在 postgresql 中）：

SELECT * 
  FROM tablexy 
 WHERE somevalue IN ("<string1>", "<string2>", "<...>", ... )

比如说，是某个值，但不是。如何获取括号中给出的不在 somevalue 列中的所有值？

谢谢 ;）

原文

I have a query like that (in postgresql):

SELECT * 
  FROM tablexy 
 WHERE somevalue IN ("<string1>", "<string2>", "<...>", ... )

say, <string1> is IN somevalue but <string2> not. How can I get all the values given in the brackets which are not IN the somevalue column?

thanks ;)

分享到QQ

分享到微博

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

发布评论

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

一百个冬季 2024-10-19 04:37:08

该解决方案将在早期的 PostgreSQL 版本（至少 8.2）中工作，并且比 Pablo 的答案更快：

SELECT listvalue FROM (VALUES ('a'),('b'),('c')) AS list(listvalue)
LEFT JOIN tablexy ON (tablexy.somevalue=list.listvalue)
WHERE tablexy.somevalue IS NULL;

在我的测试中，如果 somevalue 上有索引，则该解决方案在具有一百万行的表上在一毫秒内执行，这与巴勃罗的这需要几秒钟。

在某些情况下这可能会更快：

SELECT listvalue FROM (VALUES ('a'),('b'),('c')) AS list(listvalue)
WHERE NOT EXISTS (SELECT 1 FROM tablexy where somevalue=listvalue);

这是一个缓慢但明显的方法：

SELECT listvalue FROM (VALUES ('a'),('b'),('c')) AS list(listvalue)
EXCEPT
SELECT somevalue FROM tablexy;

希望这有帮助！

This solution will work in earlier PostgreSQL versions (at least 8.2) and also faster than Pablo's answer:

SELECT listvalue FROM (VALUES ('a'),('b'),('c')) AS list(listvalue)
LEFT JOIN tablexy ON (tablexy.somevalue=list.listvalue)
WHERE tablexy.somevalue IS NULL;

In my testing this executes in a millisecond on a table with a million rows if there's an index on somevalue, unlike Pablo's which takes seconds.

This might be faster in some cases:

SELECT listvalue FROM (VALUES ('a'),('b'),('c')) AS list(listvalue)
WHERE NOT EXISTS (SELECT 1 FROM tablexy where somevalue=listvalue);

Here's a slow but obvious approach:

SELECT listvalue FROM (VALUES ('a'),('b'),('c')) AS list(listvalue)
EXCEPT
SELECT somevalue FROM tablexy;

Hope this helps!

回复收藏 0 原文

捎一片雪花 2024-10-19 04:37:08

您可能需要为此进行单独的查询。如果您有 PostgreSQL 8.4 或更高版本，则可以使用 unnest功能：

 SELECT unnest(ARRAY['<string1>','<string2>', ...]) as element
  WHERE element not in
 SELECT somevalue FROM tablexy

You will probably need a separate query for that. If you have PostgreSQL 8.4 or later, you could use unnest function:

 SELECT unnest(ARRAY['<string1>','<string2>', ...]) as element
  WHERE element not in
 SELECT somevalue FROM tablexy

回复收藏 0 原文

夜巴黎 2024-10-19 04:37:08

在本例中，我将使用外连接，即右连接：

SELECT
    element
FROM
    tablexy 
        RIGHT JOIN (SELECT UNNEST(ARRAY['<string1>','<string2>']) AS element) AS input ON element = somevalue 
WHERE
    somevalue IS NULL;

该连接可以使用“somevalue”上的索引来提高性能。检查 EXPLAIN 以查看查询计划和索引的使用情况。

I would use an outer join, a RIGHT JOIN in this example:

SELECT
    element
FROM
    tablexy 
        RIGHT JOIN (SELECT UNNEST(ARRAY['<string1>','<string2>']) AS element) AS input ON element = somevalue 
WHERE
    somevalue IS NULL;

This one can use an index on "somevalue" for performance. Check EXPLAIN to see the queryplan and the usage of an index.

回复收藏 0 原文

~没有更多了~