SQL postgres - 比较两个数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1647385/
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
postgres - comparing two arrays
提问by pstanton
postgres has an array data type, in this case a numeric array:
postgres 有一个数组数据类型，在本例中是一个数值数组：
CREATE TABLE sal_emp (name text, pay_by_quarter integer[]);
INSERT INTO sal_emp VALUES ('one', '{1,2,3}');
INSERT INTO sal_emp VALUES ('two', '{4,5,6}');
INSERT INTO sal_emp VALUES ('three', '{2,4,6}');
SELECT * FROM sal_emp;
Result:
one, {1,2,3}
two, {4,5,6}
three, {2,4,6}
From what i can tell, you can only query an array as follows:
据我所知，您只能按如下方式查询数组：
SELECT * FROM sal_emp WHERE 4=ANY(pay_by_quarter);
SELECT * FROM sal_emp WHERE ARRAY[4,5,6]=pay_by_quarter;
which means you can select a row with the array contains a match for a single argument, or if the whole array matches an array argument.
这意味着您可以选择包含匹配单个参数的数组的行，或者如果整个数组与数组参数匹配。
I need to select a row where any member of the row's array matches any member of an argument array - kind of like an 'IN' but i can't figure out how. I've tried the following two approaches but neither work:
我需要选择一行，其中行数组的任何成员与参数数组的任何成员匹配 - 有点像“IN”，但我不知道如何。我尝试了以下两种方法，但都不起作用：
SELECT * from sal_emp WHERE ARRAY[4,5,6]=ANY(pay_by_quarter);
SELECT * from sal_emp WHERE ANY(pay_by_quarter) IN (4,5,6);
I assume i could do something with converting the array to a string but that sounds like poor solution..
我想我可以做一些将数组转换为字符串的事情，但这听起来很糟糕。
any ideas?
有任何想法吗？
回答by pstanton
figured it ... there's an && operator
想通了……有一个 && 运算符
http://www.postgresql.org/docs/8.2/static/functions-array.html
http://www.postgresql.org/docs/8.2/static/functions-array.html
"&& overlap (have elements in common) ARRAY[1,4,3] && ARRAY[2,1]"
“&&重叠（有共同的元素）ARRAY[1,4,3] && ARRAY[2,1]”
