postgresql 使用带有 IN 的 ILIKE 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12829875/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query using ILIKE with IN
提问by Utopia025
Is it possible to run a query using ILIKE with the IN function? For example:
是否可以使用带有 IN 函数的 ILIKE 运行查询？例如：
SELECT store_names
FROM stores
WHERE states ILIKE IN (SELECT location
FROM locations
WHERE stateID = 1)
I want to run the results returned from the nested query through the ILIKE function. Is there a way to do this?
我想通过 ILIKE 函数运行从嵌套查询返回的结果。有没有办法做到这一点？
采纳答案by Erwin Brandstetter
You are thinking around one more corner than needed. Can be simpler:
您正在考虑比需要更多的角落。可以更简单：
SELECT s.store_names, l.location
FROM stores s
JOIN (
SELECT location
FROM locations
WHERE stateid = 1
) l ON s.states ILIKE l.location
I only use the sub-select to improve performance. Not sure if the query planner is smart enough to use the same plan with this simpler query (and your version of Postgres):
我只使用子选择来提高性能。不确定查询计划器是否足够聪明，可以对这个更简单的查询（以及您的 Postgres 版本）使用相同的计划：
SELECT s.store_names, l.location
FROM stores s
JOIN locations l ON s.states ILIKE l.location
WHERE l.stateid = 1
Maybe you can try (with EXPLAIN ANALYZE) and report back whether they use the same plan (and perform the same)?
也许您可以尝试（使用EXPLAIN ANALYZE）并报告他们是否使用相同的计划（并执行相同的计划）？
And I suspect you actually need to add %for your purpose:
而且我怀疑您实际上需要%为您的目的添加：
... ON s.states ILIKE ('%' || l.location || '%')
