基于 Case 语句连接两个表

Question

我有两个表。我想将它们联接在同一列中，但用于联接它们的值不同。

例如，从中选择 * 表 A 联接表 B 在 A.name=B.name 上

但就我而言，A.name 和 B.name 不一样 A.name='apple' 应该连接到 B 中的每一行，其中 B.name='apple' 或 B.name='orange'。是否可以使用案例陈述或任何其他方式？

Answer 1

您可以在on子句上使用更复杂的逻辑：

from a join
b
on a.name = 'apple and b.name in ('apple', 'orange')

Answer 2

这可以使用案例语句吗？

下面是 BigQuery 标准 SQL 的简化/虚拟示例

#standardSQL
WITH `project.dataset.tableA` AS (
SELECT 1 colsA, 'apple' name union all
SELECT 2, 'lemon' union all
SELECT 3, 'peach'
), `project.dataset.tableB` AS (
SELECT 4 colsB, 'apple' name union all
SELECT 5, 'orange' union all
SELECT 6, 'melon'
)
SELECT 
a.name AS a_name,
b.name AS b_name,
colsA,
colsB
FROM `project.dataset.tableA` a
JOIN `project.dataset.tableB` b
ON CASE 
WHEN a.name = 'apple' AND b.name IN ('apple', 'orange') THEN TRUE
WHEN a.name = 'peach' AND b.name IN ('peach', 'melon') THEN TRUE
END  

输出：

Row a_name  b_name  colsA   colsB    
1   apple   apple   1       4    
2   apple   orange  1       5    
3   peach   melon   3       6      

。还是其他方式？

即使是上述的简化版本也是

#standardSQL
SELECT 
a.name AS a_name,
b.name AS b_name,
colsA,
colsB
FROM `project.dataset.tableA` a
JOIN `project.dataset.tableB` b
ON (a.name = 'apple' AND b.name IN ('apple', 'orange'))
OR (a.name = 'peach' AND b.name IN ('peach', 'melon'))    

显然具有相同的输出

Answer 3

You can make similar WHEN conditions if you have more combinations in B table  
that you would like to join like apple and orange. However if it's only apple 
and orange then this can be done using below.

SELECT A.*,B.* 
FROM tbl_A A 
JOIN tbl_B B on A.name = CASE WHEN B.Name in ('Apple','Orange')
THEN 'Apple'
ELSE B.Name
END

Answer 4

试试这种方法：

SELECT * FROM A a
INNER JOIN B b ON a.name = 'Apple' AND b.name IN ('Apple', 'Orange')