使用一个表中的整数值来查找另一个sql表中的值

我希望使用一个表中的整数值来查找另一个sql表中的字符串值。例如，我有两个表：

╔═════════╦═══════════╦═════════════════╦═══════════════════╗
║ CarType ║ CarColour ║ AbnormalCarType ║ AbnormalCarColour ║
╠═════════╬═══════════╬═════════════════╬═══════════════════╣
║       1 ║         1 ║ 1               ║ 2                 ║
║       1 ║         2 ║ null            ║ null              ║
║       2 ║         1 ║ 1               ║ 2                 ║
║       2 ║         2 ║ 1               ║ 1                 ║
╚═════════╩═══════════╩═════════════════╩═══════════════════╝

在另一张table上我有：

╔═══════════╦═══════════════╦═════════════╦═════════════════╦══╗
║ CarTypeId ║ CarTypeString ║ CarColourId ║ CarColourString ║  ║
╠═══════════╬═══════════════╬═════════════╬═════════════════╬══╣
║         1 ║ "Hyundai"     ║           1 ║ "Red"           ║  ║
║         1 ║ "Hyundai"     ║           2 ║ "Blue"          ║  ║
║         2 ║ "Toyota"      ║           1 ║ "Green"         ║  ║
║         2 ║ "Toyota"      ║           2 ║ "Yellow"        ║  ║
╚═══════════╩═══════════════╩═════════════╩═════════════════╩══╝

然后是输出

╔═════════╦═══════════╦═════════════════╦═══════════════════╗
║ CarType ║ CarColour ║ AbnormalCarType ║ AbnormalCarColour ║
╠═════════╬═══════════╬═════════════════╬═══════════════════╣
║ Hyundai ║ Red       ║ Hyundai         ║ Blue              ║
║ Hyundai ║ Blue      ║ null            ║ null              ║
║ Toyota  ║ Green     ║ Hyundai         ║ Blue              ║
║ Toyota  ║ Yellow    ║ Hyundai         ║ Red               ║
╚═════════╩═══════════╩═════════════════╩═══════════════════╝

我尝试了一个双内部连接，第一次是cartype，carcolour，然后是unnormalcartype和unnormalcarcolour，但它没有产生我想要的结果。
谢谢！

下面是bigquery标准sql


# standardSQL

SELECT b.CarTypeString AS CarType, b.CarColourString AS CarColour, 
  c.CarTypeString AS AbnormalCarType, c.CarColourString AS AbnormalCarColour
FROM `project.dataset.tableB` b  
LEFT JOIN `project.dataset.tableA` a
ON b.CarTypeId = a.CarType AND b.CarColourId = a.CarColour
LEFT JOIN `project.dataset.tableB` c
ON a.AbnormalCarType = c.CarTypeId AND a.AbnormalCarColour = c.CarColourId

如果要应用于示例中的示例数据

WITH `project.dataset.tableA` AS (
  SELECT 1 CarType, 1 CarColour, 1 AbnormalCarType, 2 AbnormalCarColour UNION ALL
  SELECT 1, 2, NULL, NULL UNION ALL
  SELECT 2, 1, 1, 2 UNION ALL
  SELECT 2, 2, 1, 1 
), `project.dataset.tableB` AS (
  SELECT 1 CarTypeId, 'Hyundai' CarTypeString, 1 CarColourId, 'Red' CarColourString UNION ALL
  SELECT 1, 'Hyundai', 2, 'Blue' UNION ALL
  SELECT 2, 'Toyota', 1, 'Green' UNION ALL
  SELECT 2, 'Toyota', 2, 'Yellow' 
)

输出如预期

Row CarType CarColour   AbnormalCarType AbnormalCarColour    
1   Hyundai Red         Hyundai         Blue     
2   Hyundai Blue        null            null     
3   Toyota  Green       Hyundai         Blue     
4   Toyota  Yellow      Hyundai         Red

使用一个表中的整数值来查找另一个sql表中的值

1条答案

相关问题

热门标签

最新问答