在ON中为左联接使用CASE语句时出现BigQuery错误
我需要一些帮助来理解我在BigQuery中遇到的错误：
如果没有连接两侧字段相等的条件，则不能使用
左外部联接。
我试图使用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我希望看到的结果是：
正如您在期望的结果中所看到的那样，当dairy的值为milk时，我希望id t2等于t1中的id列，但是当dairy的值为yogurt时，我希望dairy t2中的id等于t1中的other_id列。
我一直在寻找一个解释，但还是找不出答案。我还尝试了提供给here的解决方案，但得到了同样的错误，这就是为什么我认为我只是简单地混淆了表名或别名。
请帮帮我！
更新
我能够通过这样重写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但是现在也发生了同样的错误：
如果没有连接两侧字段相等的条件，则不能使用
左外部联接。
如果我删除了t3的连接，它就能正常工作。以下是表的更多图像和期望的结果，以防有帮助：
回答 2
Stack Overflow用户
发布于 2020-02-05 20:50:57
下面是用于BigQuery标准SQL的
#standardSQL
SELECT *,
ARRAY(
SELECT AS STRUCT *
FROM t2 b
WHERE b.id IN (a.id, a.other_id)
ORDER BY (
CASE
WHEN dairy IN ('milk', 'yogurt') THEN 1
ELSE 2
END
)
LIMIT 1
)[SAFE_OFFSET(0)] AS t2
FROM t1 a 如果要应用于您的问题中的样本/虚拟数据-结果是
Row dairy id other_id t2.color t2.id
1 milk 1 2 blue 1
2 yogurt 3 4 red 4
3 cheese 5 6 Stack Overflow用户
发布于 2020-02-05 21:54:07
通过将联接和关联逻辑分解为单独的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_t3https://stackoverflow.com/questions/60083396
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号