PostgreSQL自连接/CASE语句问题：同表取分组首个值报错

需求分析 #

现有数据表结构及数据如下：

id sort value   
1   1     1   
2   1     8    
3   2     0    
4   1     2    

需要按id排序后，为每条记录生成new列，值为该记录所属sort分组中首次出现的value（即分组内最小id对应的value），期望输出：

id sort value new  
1   1     1    1    
2   1     8    1      
3   2     0    0      
4   1     2    1   

最优解法：使用窗口函数FIRST_VALUE #

窗口函数是实现这类分组取首值需求最简洁高效的方式，无需自连接即可完成：

SELECT 
    id,
    sort,
    value,
    FIRST_VALUE(value) OVER (PARTITION BY sort ORDER BY id) AS new
FROM your_table_name -- 替换为你的实际表名
ORDER BY id;

• PARTITION BY sort：按sort字段分组
• ORDER BY id：确保分组内按id升序排列，保证取到的是首次出现的记录
• FIRST_VALUE(value)：提取分组内排序后的第一个value值

修复自连接方案的"relation not found"错误 #

你提到的"relation not found"错误，通常是自连接时表别名未正确定义/引用或表名拼写错误导致的。以下是正确的自连接写法：

SELECT 
    t1.id,
    t1.sort,
    t1.value,
    t2.value AS new
FROM your_table_name t1
JOIN (
    -- 子查询：获取每个sort分组中最小id对应的value
    SELECT sort, value
    FROM your_table_name
    WHERE (sort, id) IN (
        SELECT sort, MIN(id) 
        FROM your_table_name 
        GROUP BY sort
    )
) t2 ON t1.sort = t2.sort
ORDER BY t1.id;

常见错误原因排查 #

1. 未指定表别名：自连接时必须给同一张表起不同别名（如t1、t2），否则数据库无法区分两个引用的表
2. 子查询中列引用模糊：比如子查询内的WHERE sort = sort会导致逻辑错误，需明确指定表别名（如t1.sort = t2.sort）
3. 表名拼写错误：确保所有地方引用的表名和实际数据库中的表名完全一致

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