如何通过SQL查询获取匹配itemid/batch的originalref字段值

原始查询结果 #

期望输出结果 #

方法1：使用窗口函数（推荐，兼容MySQL 8+、PostgreSQL、SQL Server等） #

利用窗口函数在itemid+batch分组内，提取referencecategory=3的referenceid值，无需额外表连接，性能更优：

SELECT 
    itemid,
    referencecategory,
    referenceid,
    batch,
    MAX(CASE WHEN referencecategory = 3 THEN referenceid END) OVER (PARTITION BY itemid, batch) AS originalref
FROM 
    your_table_name;

方法2：使用自连接（兼容低版本数据库） #

通过表自连接，匹配相同itemid+batch且referencecategory=3的记录，需确保每个itemid+batch组合仅有一条referencecategory=3的行：

SELECT 
    t1.itemid,
    t1.referencecategory,
    t1.referenceid,
    t1.batch,
    t2.referenceid AS originalref
FROM 
    your_table_name t1
JOIN 
    your_table_name t2 
    ON t1.itemid = t2.itemid 
    AND t1.batch = t2.batch 
    AND t2.referencecategory = 3;

注意事项 #

• 替换语句中的your_table_name为实际表名，若原始数据来自复杂查询，可将原始查询作为子查询替换your_table_name；
• 窗口函数方法会保留所有原始行，自连接方法若存在多个referencecategory=3的匹配行，会产生重复结果。

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