SQL Postgres - 返回 2 个数组的交集的函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/756871/
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 - Function to return the intersection of 2 ARRAYs?
提问by Rory
In postgresql, you can use the && operator to return t (true) if two arrays have common members, i.e. they overlap. Is there a function/operator that will return what those common members are?
在 postgresql 中，如果两个数组具有公共成员，即它们重叠，您可以使用 && 运算符返回 t (true)。是否有一个函数/运算符会返回那些公共成员是什么？
i.e. something like this
即像这样的东西
select arrray_intersection(ARRAY[1, 4, 2], ARRAY[2, 3]);
ARRAY[2]
回答by bart
Since 8.4, there are useful builtins in Postgreswhich make the function from the first answereasier and possibly faster (that's what EXPLAIN tells me, anyway: "(cost=0.00..0.07 rows=1 width=64)" for this query vs. "(cost=0.00..60.02 rows=1 width=64)" for the original one).
从 8.4 开始，Postgres 中有一些有用的内置函数，它们使第一个答案中的函数变得更容易并且可能更快（这就是 EXPLAIN 告诉我的，无论如何：“(cost=0.00..0.07 rows=1 width=64)”这个查询 vs . "(cost=0.00..60.02 rows=1 width=64)" 为原始的)。
The simplified code is:
简化的代码是：
SELECT ARRAY
(
SELECT UNNEST(a1)
INTERSECT
SELECT UNNEST(a2)
)
FROM (
SELECT array['two', 'four', 'six'] AS a1
, array['four', 'six', 'eight'] AS a2
) q;
and yeah, you can turn it into a function:
是的，你可以把它变成一个函数：
CREATE FUNCTION array_intersect(anyarray, anyarray)
RETURNS anyarray
language sql
as $FUNCTION$
SELECT ARRAY(
SELECT UNNEST()
INTERSECT
SELECT UNNEST()
);
$FUNCTION$;
which you can call as
你可以称之为
SELECT array_intersect(array['two', 'four', 'six']
, array['four', 'six', 'eight']);
But you can just as well call it inline too:
但是您也可以将其称为内联：
SELECT array(select unnest(array['two', 'four', 'six']) intersect
select unnest(array['four', 'six', 'eight']));
回答by dwc
回答by ncank
one another method..
另一种方法..
SELECT ARRAY( SELECT * FROM UNNEST( ) WHERE UNNEST = ANY( ) );
回答by mnv
If you don't mind installing an extension, the intarray extensionprovides the &operator to do this as @dwc pointed out.:
如果您不介意安装扩展，则intarray 扩展提供了&操作员来执行此操作，正如@dwc 指出的那样。：
SELECT ARRAY[1, 4, 2] & ARRAY[2, 3];
Returns {2}.
返回{2}。
回答by mnv
You can use this function:
您可以使用此功能：
CREATE OR REPLACE FUNCTION intersection(anyarray, anyarray) RETURNS anyarray as $$
SELECT ARRAY(
SELECT [i]
FROM generate_series( array_lower(, 1), array_upper(, 1) ) i
WHERE ARRAY[[i]] &&
);
$$ language sql;
It should work with any kind of array, and you can use it like this:
它应该适用于任何类型的数组，您可以像这样使用它：
SELECT intersection('{4,2,6}'::INT4[], '{2,3,4}'::INT4[]);
回答by Quassnoi
SELECT ARRAY
(
SELECT a1[s]
FROM generate_series(array_lower(a1, 1), array_upper(a1, 1)) s
INTERSECT
SELECT a2[s]
FROM generate_series(array_lower(a2, 1), array_upper(a2, 1)) s
)
FROM (
SELECT array['two', 'four', 'six'] AS a1, array['four', 'six', 'eight'] AS a2
) q
Works on non-integer arrays too.
也适用于非整数数组。
