postgresql 从 Postgres 字段的多个值中选择任何一个

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/10302541/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-10 23:27:48  来源:igfitidea点击:

Select from any of multiple values from a Postgres field

postgresql

提问by Winfield Trail

I've got a table that resembles the following:

我有一个类似于以下内容的表:

WORD    WEIGHT   WORDTYPE
a       0.3      common
the     0.3      common
gray    1.2      colors
steeple 2        object

I need to pull the weights for several different words out of the database at once. I could do:

我需要一次从数据库中提取几个不同单词的权重。我可以:

SELECT * FROM word_weight WHERE WORD = 'a' OR WORD = 'steeple' OR WORD='the';

but it feels ugly and the code to generate the query is obnoxious. I'm hoping that there's a way I can do something like (pseudocode):

但感觉很难看,生成查询的代码令人讨厌。我希望有一种方法可以做(伪代码)之类的事情:

SELECT * FROM word_weight WHERE WORD = 'a','the';

回答by Mark Wenzel

You are describing the functionality of the in clause.

您正在描述 in 子句的功能。

select * from word_weight where word in ('a', 'steeple', 'the');

select * from word_weight where word in ('a', 'steeple', 'the');

回答by Quassnoi

If you want to pass the whole list in a single parameter, use array datatype:

如果要在单个参数中传递整个列表,请使用数组数据类型:

SELECT  *
FROM    word_weight
WHERE   word = ANY('{a,steeple,the}'); -- or ANY('{a,steeple,the}'::TEXT[]) to make explicit array conversion

回答by shubham mishra

If you are not sure about the value and even not sure whether the field will be an empty string or even null then,

如果您不确定该值,甚至不确定该字段是空字符串还是空字符串,那么,

.where("column_1 ILIKE ANY(ARRAY['','%abc%','%xyz%']) OR column_1 IS NULL")

Above query will cover all possibility.

以上查询将涵盖所有可能性。