Snowflake/SQL中嵌套CASE语句实现需求遇阻求助

现有表结构与数据 #

data_set表的结构和数据如下：

定义的CTE表 #

已创建的city_speed_limits CTE：

with city_speed_limits(Name,Capacity,Owner,Toll_Price,Max_Speed) as (
    select * from values
        ('Bus','Heavy','City',null,'50 kmph'),
        ('Car','Light','Private','Toll','60 kmph'),
        ('Bike','Light','Private',null,null)
)

期望输出 #

需要生成的目标结果表：

当前查询的问题 #

你提供的查询存在两处核心问题：

1. 多余的iff(b.Max_Speed is not null, 'UNKNOWN', 'KNOWN')语句，既存在语法错误（未加逗号分隔），又不符合需求逻辑
2. CASE语句逻辑错误：未优先判断Max_Speed是否为null，且错误限制仅处理Bus和Car，同时用CROSS JOIN会产生多余的交叉行

修正后的SQL查询 #

with city_speed_limits(Name,Capacity,Owner,Toll_Price,Max_Speed) as (
    select * from values
        ('Bus','Heavy','City',null,'50 kmph'),
        ('Car','Light','Private','Toll','60 kmph'),
        ('Bike','Light','Private',null,null)
)
select 
    a.ID as ID, 
    b.Name as Name,
    b.Owner as Owner,
    b.Max_Speed as Speed,
    CASE
        -- 优先判断Max_Speed为空的情况，直接返回UNKNOWN
        WHEN b.Max_Speed IS NULL THEN 'UNKNOWN'
        -- 根据Description字段映射对应值
        WHEN a.Description = 'Neg' THEN 'NEGATIVE'
        WHEN a.Description = 'Pos' THEN 'POSITIVE'
        WHEN a.Description = 'Other' THEN 'UNKNOWN'
    END as Affordability
from data_set a
-- 用INNER JOIN匹配交通工具名称，避免交叉冗余行
inner join city_speed_limits b on a.Can_Afford = b.Name

关键修正说明 #

• 移除无效的iff语句，统一用CASE处理所有逻辑，贴合需求优先级
• 调整CASE判断顺序：先检查Max_Speed是否为null，直接返回'UNKNOWN'
• 替换CROSS JOIN为INNER JOIN，通过Can_Afford和Name关联，确保结果行匹配正确
• 去掉对b.Name的不必要限制，逻辑覆盖所有交通工具类型

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