按branch_id分组，将branch_name列空值替换为组内首行值

原始数据表 #

brnach_id  branch_loc branch_name
1222         HYD        COMPUTER
1222         HYD        NULL
1222         HYD        COMPUTER
1222         HYD        NULL
1222         HYD        NULL
1333         BLR        ENGINEERING
1333         HYD        NULL
1333         BLR        NULL
1333         CHI        NULL
1444         PUN        MARKETING
1444         HYD        NULL

需求说明 #

按branch_id分组，将每组内branch_name列的所有行统一替换为该组首行的branch_name值，预期结果如下：

brnach_id  branch_loc branch_name
1222         HYD        COMPUTER
1222         HYD        COMPUTER
1222         HYD        COMPUTER
1222         HYD        COMPUTER
1222         HYD        COMPUTER
1333         BLR        ENGINEERING
1333         HYD        ENGINEERING
1333         BLR        ENGINEERING
1333         CHI        ENGINEERING
1444         PUN        MARKETING
1444         HYD        MARKETING

不同数据库的实现方案 #

MySQL 版本

使用FIRST_VALUE()窗口函数，按branch_id分组后取组内首行的branch_name：

SELECT 
    brnach_id,
    branch_loc,
    FIRST_VALUE(branch_name) OVER (PARTITION BY brnach_id ORDER BY (SELECT 1)) AS branch_name
FROM your_table_name;

说明：ORDER BY (SELECT 1)用于保留原始行顺序，确保取到的是每组的首行值

PostgreSQL 版本

同样用FIRST_VALUE()窗口函数，用ctid维持原始行顺序：

SELECT 
    brnach_id,
    branch_loc,
    FIRST_VALUE(branch_name) OVER (PARTITION BY brnach_id ORDER BY ctid) AS branch_name
FROM your_table_name;

说明：如果表有明确排序字段，可替换ctid为该字段

SQL Server 版本

使用FIRST_VALUE()窗口函数：

SELECT 
    brnach_id,
    branch_loc,
    FIRST_VALUE(branch_name) OVER (PARTITION BY brnach_id ORDER BY (SELECT NULL)) AS branch_name
FROM your_table_name;

兼容旧版数据库的方案（无窗口函数）

如果数据库不支持窗口函数，用子查询获取每组首行值：

-- MySQL/PostgreSQL
SELECT 
    t.brnach_id,
    t.branch_loc,
    (SELECT branch_name FROM your_table_name WHERE brnach_id = t.brnach_id LIMIT 1) AS branch_name
FROM your_table_name t;

-- SQL Server
SELECT 
    t.brnach_id,
    t.branch_loc,
    (SELECT TOP 1 branch_name FROM your_table_name WHERE brnach_id = t.brnach_id) AS branch_name
FROM your_table_name t;

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