google-bigquery - 在 ON 中使用 CASE 语句进行 LEFT JOIN 时出现 BigQuery 错误

Question

我正在寻求一些帮助来理解我在 BigQuery 中遇到的这个错误：

LEFT OUTER JOIN 不能在没有连接两边的字段相等的条件下使用。

我正在尝试使用 case 语句来根据左表行中的值更改选择加入的行。我在其他一些地方做类似的事情并且它有效，所以我的一部分认为我可能在表别名和列名方面犯了一个错误，但我无法弄清楚。这是我正在尝试做的一个最小示例：

WITH t1 AS (
  SELECT "milk" AS dairy,
   1 AS id,
   2 AS other_id

   UNION ALL

   SELECT "yogurt" AS dairy,
   3 AS id,
   4 AS other_id

   UNION ALL

   SELECT "cheese" AS dairy,
   5 AS id,
   6 AS other_id
),

t2 AS (
  SELECT "blue" AS color,
  1 AS id

  UNION ALL

  SELECT "red" AS color,
  4 AS id
)

SELECT
  t1.*, t2
FROM t1
LEFT JOIN t2 ON
  CASE
    WHEN t1.dairy = 'milk' THEN t1.id = t2.id
    WHEN t1.dairy = 'yogurt' THEN t1.other_id = t2.id
  END

我想看到的结果是：

正如您在所需结果中看到的那样，当值为dairyis时milk，我希望idfromt2等于 in 中的id列t1，但是当值为dairyis时yogurt，我希望idfromt2等于 in 中的other_id列t1。

我一直在寻找解释，但无法弄清楚。我也尝试了这里提供的解决方案，但得到了同样的错误，这就是为什么我认为我只是用表名或别名搞砸了一些东西。

请帮忙！

更新

通过以这种方式重写 case 语句，我能够摆脱错误：

SELECT
  t1.*, t2
FROM t1
LEFT JOIN t2 ON
  CASE
    WHEN t1.dairy = 'milk' THEN t1.id
    WHEN t1.dairy = 'yogurt' THEN t1.other_id
  END = t2.id

但是，在我真正的问题中，我需要以类似的方式加入第三张桌子。如果t2.color是blue，我想加入基于t2.id = t3.id，但如果t2.color是，red我想加入基于t2.id = t3.other_id。一旦我这样做，就会发生同样的错误。这是我尝试的完整示例：

WITH t1 AS (
  SELECT "milk" AS dairy,
   1 AS id,
   2 AS other_id

   UNION ALL

   SELECT "yogurt" AS dairy,
   3 AS id,
   4 AS other_id

   UNION ALL

   SELECT "cheese" AS dairy,
   5 AS id,
   6 AS other_id
),

t2 AS (
  SELECT "blue" AS color,
  1 AS id

  UNION ALL

  SELECT "red" AS color,
  4 AS id
),

t3 AS (
  SELECT "sunny" AS weather,
  1 AS id,
  10 AS other_id

  UNION ALL

  SELECT "cloudy" AS weather,
  11 AS id,
  4 AS other_id
)

SELECT
  t1.*, t2, t3
FROM t1
LEFT JOIN t2 ON
  CASE
    WHEN t1.dairy = 'milk' THEN t1.id
    WHEN t1.dairy = 'yogurt' THEN t1.other_id
  END = t2.id
LEFT JOIN t3 ON
  CASE
   WHEN t2.color = 'blue' THEN t3.id
   WHEN t2.color = 'red' THEN t3.other_id
  END = t2.id

但是现在发生了同样的错误：

LEFT OUTER JOIN 不能在没有连接两边的字段相等的条件下使用。

如果我删除 的加入t3，它可以正常工作。以下是表格的更多图像和所需的结果，以防有帮助：

标签： google-bigquery

Answer 1

通过将连接和相关逻辑分解为单独的 CTE，我能够用 3 个表回答您更新的问题。

WITH t1 AS (
  SELECT "milk" AS dairy, 1 AS id, 2 AS other_id UNION ALL
  SELECT "yogurt", 3, 4 UNION ALL
  SELECT "cheese", 5, 6
),
t2 AS (
  SELECT "blue" AS color, 1 AS id UNION ALL
  SELECT "red", 4
),
t3 AS (
  SELECT "sunny" AS weather, 1 as id, 10 as other_id UNION ALL
  SELECT "cloudy", 11, 4
),
join_t1_t2 as (
  select
    t1.*,
    case 
      when t1.dairy = 'milk' then milk.color
      when t1.dairy = 'yogurt' then yogurt.color
      else null
    end as t2_color,
    case 
      when t1.dairy = 'milk' then milk.id
      when t1.dairy = 'yogurt' then yogurt.id
      else null
    end as t2_id
  from t1
  left join t2 milk on t1.id = milk.id
  left join t2 yogurt on t1.other_id = yogurt.id
),
join_t1_t2_t3 as (
  select
    join_t1_t2.*,
    case 
      when t2_color = 'blue' then blue.id
      when t2_color = 'red' then red.id
      else null
    end as t3_id,
    case 
      when t2_color = 'blue' then blue.other_id
      when t2_color = 'red' then red.other_id
      else null
    end as t3_other_id,
    case 
      when t2_color = 'blue' then blue.weather
      when t2_color = 'red' then red.weather
      else null
    end as t3_weather,
  from join_t1_t2
  left join t3 blue on t2_id = blue.id
  left join t3 red on t2_id = red.other_id
)
select * from join_t1_t2_t3