我有这样的数据:
id col1 col2
-----------------
1 [1,2] [2,3]
2 [4,4,6] [6,7]
我想要这样的数据:
id col3
---------
1 [2]
2 [6]
有什么聪明的解决方案吗?
您可以使用INTERSECT DISTINCT
-- build example table
WITH example as (
SELECT
* FROM UNNEST([
STRUCT([1,2] as col1, [2,3] as col2),
STRUCT([4,4,6],[6,7])
])
)
-- INTERSECT per row on two arrays
SELECT
ARRAY(SELECT * FROM example.col1
INTERSECT DISTINCT
(SELECT * FROM example.col2)
) AS result
FROM example
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)