谢谢大家的回答。我有一个类似于bigquery的版本，最后使用了下面的代码，这与您的建议非常接近。

WITH table1 AS (SELECT field1 FROM UNNEST(['value1', ...'value2']) AS field1),
table2 AS (SELECT field2 FROM UNNEST(['v1', 'v2']) AS field2)
SELECT
field1,
(SELECT LOGICAL_OR(REGEXP_CONTAINS(field1, CONCAT(r'(?i)\b', field2, r'\b'))) FROM table2) AS has_table2_match
FROM
table1

再次感谢您的建议。谢谢你。

下面的示例是针对bigquery标准sql的

# standardSQL

SELECT 
  Field1, STRING_AGG(Field2) AS contained
FROM `table1`
JOIN `table2`
ON REGEXP_CONTAINS(Field1, Field2) 
GROUP BY Field1

你可以用下面的虚拟数据测试/玩上面的游戏

# standardSQL

WITH `table1` AS (
  SELECT 'abc' Field1 UNION ALL
  SELECT 'xyz'
),
`table2` AS (
  SELECT 'a' Field2 UNION ALL
  SELECT 'x' UNION ALL
  SELECT 'y' UNION ALL
  SELECT 'z'
)
SELECT 
  Field1, STRING_AGG(Field2) AS contained
FROM `table1`
JOIN `table2`
ON REGEXP_CONTAINS(Field1, Field2) 
GROUP BY Field1

结果为

Field1  contained    
abc     a    
xyz     x,y,z

第二个选项更符合您最初的期望（在语法模式方面），但需要使用 SQL UDF :

# standardSQL

CREATE TEMP FUNCTION Check_Contains(str STRING, arr ARRAY<STRING>) AS ((
  SELECT STRING_AGG(item) AS contained
  FROM UNNEST(arr) item
  WHERE REGEXP_CONTAINS(str, item) 
));
SELECT Field1, 
  Check_Contains(Field1, ARRAY(SELECT Field2 FROM `table2`)) AS contained
FROM `table1`

测试/播放-您可以使用下面的

# standardSQL

CREATE TEMP FUNCTION Check_Contains(str STRING, arr ARRAY<STRING>) AS ((
  SELECT STRING_AGG(item) AS contained
  FROM UNNEST(arr) item
  WHERE REGEXP_CONTAINS(str, item) 
));
WITH `table1` AS (
  SELECT 'abc' Field1 UNION ALL
  SELECT 'xyz' UNION ALL
  SELECT 'vwu'
),
`table2` AS (
  SELECT 'a' Field2 UNION ALL
  SELECT 'x' UNION ALL
  SELECT 'y' UNION ALL
  SELECT 'z'
)
SELECT Field1, 
  Check_Contains(Field1, ARRAY(SELECT Field2 FROM `table2`)) AS contained
FROM `table1`

输出为

Field1  contained    
abc     a    
xyz     x,y,z    
vwu     null

多一个选项（不带自定义项）

# standardSQL

SELECT Field1, 
  ARRAY_TO_STRING(REGEXP_EXTRACT_ALL(Field1, (SELECT STRING_AGG(Field2, '|') FROM `table2`)), ',') AS contained
FROM `table1`

您可以使用与上述相同的虚拟数据（前两个选项）

你需要使用 CROSS JOIN 相反。请注意，如果表很大，这将很慢，因为您要计算所有行的叉积：

SELECT
  t1.Field1,
  REGEXP_CONTAINS(t1.Field1, t2.Field2)
FROM table1 AS t1
CROSS JOIN table2 AS t2;

如果你想知道 Field1 / Field2 对于此表达式为true的对，可以使用带筛选器的查询：

SELECT
  t1.Field1,
  t2.Field2
FROM table1 AS t1
CROSS JOIN table2 AS t2
WHERE REGEXP_CONTAINS(t1.Field1, t2.Field2);