SQL 连接通配符列/连接 col1 和 col2 if col1 在表中 else join on col2

Question

假设我是一家根据客户姓名销售星座的公司。我有一张包含姓氏、姓氏和星座文本的表格。由于我无法涵盖每一个姓名组合，因此我经常将姓氏存储为 NULL，作为包罗万象的值。

Horoscope DB

sur | fam | horoscope
----------------------
John| Doe  | text1
Jane| Doe  | text2
NULL| Doe  | text3
Ike | Smith| text4
NULL| Smith| text5

以及客户列表

customer DB

sur | fam
---------
John| Doe
Jack| Doe
Lisa| Smith
Carl| Smith

现在我们需要为每个客户匹配一个星座。如果姓氏和姓氏完全匹配，则两者都匹配，但如果没有完全匹配，则仅匹配姓氏，因此结果将是:

Customer horoscope DB

sur | fam | horoscope
----------------------
John| Doe  | text1
Jack| Doe  | text3
Lisa| Smith| text5
Carl| Smith| text5

如果我执行正常的LEFT JOIN USING(sur, fam)，我只会得到 John 的匹配项。如果我使用LEFT JOIN USING(fam)我会得到很多重复。我需要设置一些条件，但我不知道如何设置。

如果需要，我愿意更改我的包罗万象的值，或者将其编码为单独的列。

具体来说，我正在使用 Google Big Query。 I have set up a DB-fiddle, that you are welcome to use

Answer 1

以下适用于 BigQuery 标准 SQL

#standardSQL
SELECT c.sur, c.fam,
  ARRAY_AGG(horoscope ORDER BY h.sur DESC LIMIT 1)[OFFSET(0)] horoscope
FROM `project.dataset.customer` c
JOIN `project.dataset.horoscope` h
ON c.fam = h.fam
AND c.sur = IFNULL(h.sur, c.sur)
GROUP BY c.sur, c.fam

您可以使用示例中的示例数据来测试、使用上面的内容，如下例所示

#standardSQL
WITH `project.dataset.horoscope` AS (
  SELECT 'John' sur,'Doe' fam, 'text1' horoscope UNION ALL
  SELECT 'Jane', 'Doe', 'text2' UNION ALL
  SELECT NULL, 'Doe', 'text3' UNION ALL
  SELECT 'Ike', 'Smith', 'text4' UNION ALL
  SELECT NULL, 'Smith', 'text5' 
), `project.dataset.customer` AS (
  SELECT 'John' sur, 'Doe' fam UNION ALL
  SELECT 'Jack', 'Doe' UNION ALL
  SELECT 'Lisa', 'Smith' UNION ALL
  SELECT 'Carl', 'Smith' 
)
SELECT c.sur, c.fam,
  ARRAY_AGG(horoscope ORDER BY h.sur DESC LIMIT 1)[OFFSET(0)] horoscope
FROM `project.dataset.customer` c
JOIN `project.dataset.horoscope` h
ON c.fam = h.fam
AND c.sur = IFNULL(h.sur, c.sur)
GROUP BY c.sur, c.fam  

结果

Row sur     fam     horoscope    
1   John    Doe     text1    
2   Jack    Doe     text3    
3   Lisa    Smith   text5    
4   Carl    Smith   text5