SQL查询简化需求：从tb1推导main_tbl的COL_VAL列值并精简代码行数

嘿，我来帮你把这段重复度很高的SQL给精简一下！原查询里反复写了结构几乎一模一样的子查询，不仅代码冗余，后期维护也麻烦。咱们可以通过统一标记优先级+一次窗口函数筛选的方式，用更少的代码实现完全相同的逻辑。

优化后的查询代码 #

SELECT 
    d.COL1, 
    d.COL2, 
    d.COL3, 
    CAST(t.col4 AS STRING) AS COL_VAL
FROM db.main_tbl d
LEFT JOIN (
    SELECT 
        col1,
        col4,
        ROW_NUMBER() OVER(
            PARTITION BY col1 
            ORDER BY priority ASC, col5 DESC
        ) AS rnk
    FROM (
        SELECT 
            *,
            -- 完全匹配原查询COALESCE的取值优先级：MOB Y > LN Y > MOB N > LN N
            CASE 
                WHEN col2 = 'MOB' AND col3 = 'Y' THEN 1
                WHEN col2 = 'LN' AND col3 = 'Y' THEN 2
                WHEN col2 = 'MOB' AND col3 = 'N' THEN 3
                WHEN col2 = 'LN' AND col3 = 'N' THEN 4
            END AS priority
        FROM db.tb1
        -- 保留原查询涉及的筛选范围
        WHERE col2 IN ('LN', 'MOB') AND col3 IN ('Y', 'N')
    ) t1
) t ON d.col1 = t.col1 AND t.rnk = 1

逻辑说明 #

1. 优先级标记：用CASE语句给tb1中符合条件的记录打上优先级标签，数字越小代表优先级越高，完全对应原查询COALESCE的取值顺序；
2. 窗口函数筛选：按col1分组，先按优先级升序（锁定最高优先级），再按col5降序（取同优先级里最新的记录），用ROW_NUMBER()给每组排序后只保留第一条；
3. 关联主表：最后只需要和main_tbl做一次左关联，直接拿到目标COL_VAL的值，省去了原查询中4次重复的左连接和嵌套子查询。

这样改完之后，代码行数减少了一半还多，逻辑更清晰，后期如果要调整优先级或筛选条件，只需要修改CASE语句和WHERE条件就行，维护起来方便多啦！

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