SQL新手求助：基于proj_id和template_type填充instance_name并查询目标数据

我是SQL初学者，大部分查询场景都能搞定，但现在卡在一个需求上。现有数据表及数据如下：

需求说明 #

当两条记录的proj_id和template_type都相同时（比如第一条和第二条，proj_id=11111、template_type=jenkins），把同组里非空的instance_name（这里是mnc）填充到该组内instance_name为空的记录中，之后查询出tech_name='teradata'且instance_name='mnc'的那条记录。

我的尝试（未生效） #

where 
t1.instance_name=( 
  select max(instance_name )
  FROM test.table t2 
  where t2.project_id=t1.project_id)

解决方法 #

方法一：使用窗口函数填充空值（推荐）

窗口函数可以直接按proj_id和template_type分组，获取组内非空的instance_name，再筛选目标记录：

SELECT *
FROM (
    SELECT 
        id,
        proj_id,
        project_name,
        template_type,
        tech_name,
        -- 取同组内非空的instance_name，MAX会自动忽略空值
        MAX(instance_name) OVER (PARTITION BY proj_id, template_type) AS filled_instance_name
    FROM test.table
) AS t
WHERE t.tech_name = 'teradata' AND t.filled_instance_name = 'mnc';

方法二：使用关联子查询填充空值

通过子查询先获取每组的非空instance_name，再关联原表填充空值：

SELECT 
    t1.id,
    t1.proj_id,
    t1.project_name,
    t1.template_type,
    t1.tech_name,
    COALESCE(t1.instance_name, t2.instance_name) AS filled_instance_name
FROM test.table t1
LEFT JOIN (
    SELECT proj_id, template_type, MAX(instance_name) AS instance_name
    FROM test.table
    WHERE instance_name IS NOT NULL
    GROUP BY proj_id, template_type
) t2 ON t1.proj_id = t2.proj_id AND t1.template_type = t2.template_type
WHERE t1.tech_name = 'teradata' AND COALESCE(t1.instance_name, t2.instance_name) = 'mnc';

原语句问题分析 #

1. 字段名错误：原表字段是proj_id，你写成了project_id；
2. 分组条件不全：只关联了proj_id，没包含template_type，无法准确匹配同组记录；
3. 逻辑错误：直接用where t1.instance_name = (...)会过滤掉原本instance_name为空的记录，达不到填充后查询的目的。

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