如何将SQL中的IF语句转为CASE语句并用于BigQuery的WHERE子句

首先将原IF语句转换为BigQuery支持的CASE语句，由于多个地址值对应同一ZIP，可使用IN简化条件判断：

CASE
  WHEN address IN ('US\\Madison Drive', 'US\\123 Madison Dr', 'US\\123 Madison-Dr') THEN 'ZIP1234'
  ELSE 'ZIP9999'
END

关键注意点 #

• BigQuery中字符串内的反斜杠需要转义，因此原地址中的\需替换为\\
• 使用IN合并相同结果的多条件，比重复编写WHEN子句更高效简洁

整合到WHERE条件的完整查询 #

假设需求是筛选出根据地址规则应匹配ZIP1234的记录，完整SQL如下：

SELECT a.ID, a.NAME, a.Address, a.ZIP
FROM table_A a
LEFT JOIN table_B b ON a.ID = b.ID
WHERE CASE
        WHEN a.address IN ('US\\Madison Drive', 'US\\123 Madison Dr', 'US\\123 Madison-Dr') THEN 'ZIP1234'
        ELSE 'ZIP9999'
      END = 'ZIP1234'

如果需要将计算后的ZIP作为字段返回，同时保留过滤逻辑，可调整为：

SELECT 
  a.ID, 
  a.NAME, 
  a.Address, 
  a.ZIP,
  CASE
    WHEN a.address IN ('US\\Madison Drive', 'US\\123 Madison Dr', 'US\\123 Madison-Dr') THEN 'ZIP1234'
    ELSE 'ZIP9999'
  END AS calculated_ZIP
FROM table_A a
LEFT JOIN table_B b ON a.ID = b.ID
WHERE CASE
        WHEN a.address IN ('US\\Madison Drive', 'US\\123 Madison Dr', 'US\\123 Madison-Dr') THEN 'ZIP1234'
        ELSE 'ZIP9999'
      END = 'ZIP1234'

内容的提问来源于stack exchange，提问作者S M