如何用另一查询结果模糊过滤BigQuery中的表？

方法一：WITH子句+EXISTS关联 #

先通过WITH子句生成table1的拼接结果集，再用EXISTS检查table2的field_b是否包含任意一个拼接结果，同时满足field_a的条件。

WITH table1_results AS (
  SELECT CONCAT(INITCAP(LEFT(field1,3)), "_", INITCAP(field2), "_", INITCAP(field3)) as result
  FROM `project1.dataset1.table1`
)
SELECT t2.*
FROM `project1.dataset2.table2` t2
WHERE t2.field_a IN ('value1', 'value2')
AND EXISTS (
  SELECT 1
  FROM table1_results t1r
  WHERE t2.field_b LIKE CONCAT('%', t1r.result, '%')
);

方法二：JOIN+模糊匹配 #

如果需要展示匹配到的具体拼接结果，可以用JOIN，记得加DISTINCT避免同一行table2因匹配多个结果而重复输出。

WITH table1_results AS (
  SELECT CONCAT(INITCAP(LEFT(field1,3)), "_", INITCAP(field2), "_", INITCAP(field3)) as result
  FROM `project1.dataset1.table1`
)
SELECT DISTINCT t2.*
FROM `project1.dataset2.table2` t2
JOIN table1_results t1r
ON t2.field_b LIKE CONCAT('%', t1r.result, '%')
WHERE t2.field_a IN ('value1', 'value2');

之前错误的原因 #

• 标量子查询报错：标量子查询要求只能返回1行1列，但你的table1生成的是多行结果，所以触发“Scalar subquery produced more than one element”错误，不能直接用这种方式过滤。
• WITH子句语法错误：WITH子句必须放在整个查询的最开头，不能插在SELECT语句中间，你之前的位置不对才导致语法报错。

内容的提问来源于stack exchange，提问作者Barbi