SQL查询每个产品最新两条价格记录行列转换获取对应日期价格

实现思路 #

你之前仅用max/min聚合无法匹配对应价格，是因为分组聚合无法直接关联到非聚合字段的对应行值。推荐优先使用条件聚合+单次开窗的方案，性能优于多次子查询关联的写法。

最优实现代码 #

SELECT
    prodnumb AS 产品编号,
    MAX(CASE WHEN rn = 1 THEN collectdate END) AS 最新日期,
    MAX(CASE WHEN rn = 1 THEN price END) AS 最新价格,
    MAX(CASE WHEN rn = 2 THEN collectdate END) AS 次新日期,
    MAX(CASE WHEN rn = 2 THEN price END) AS 次新价格
FROM (
    SELECT
        id,
        prodnumb,
        collectdate,
        price,
        ROW_NUMBER() OVER(PARTITION BY prodnumb ORDER BY id DESC) AS rn
    FROM product
    -- 自定义过滤条件可写在此处，例如 WHERE deviceId > 0
) A
WHERE rn < 3
GROUP BY prodnumb;

逻辑说明 #

• 内层查询逻辑和你原有基础查询一致，按产品编号分区、id倒序排序打行号，筛选每个产品的前2条记录
• 外层按产品编号分组后，通过CASE语句匹配对应行号的采集日期、价格字段，用MAX聚合实现两行数据压扁为单行的效果，每个产品分组下对应行号的记录仅1条，聚合不会修改原值

参考关联方案修正版 #

你找到的多表关联方案逻辑可运行，但原代码存在多余关联、重复计算的问题，修正后代码如下：

SELECT
    f.prodnumb AS 产品编号,
    f.collectdate AS 最新日期,
    f.price AS 最新价格,
    s.collectdate AS 次新日期,
    s.price AS 次新价格
FROM (
    SELECT
        prodnumb,
        collectdate,
        price
    FROM (
        SELECT
            prodnumb,
            collectdate,
            price,
            ROW_NUMBER() OVER(PARTITION BY prodnumb ORDER BY id DESC) AS rn
        FROM product
        -- 自定义过滤条件可写在此处，例如 WHERE deviceId > 0
    ) A
    WHERE rn = 1
) f
LEFT JOIN (
    SELECT
        prodnumb,
        collectdate,
        price
    FROM (
        SELECT
            prodnumb,
            collectdate,
            price,
            ROW_NUMBER() OVER(PARTITION BY prodnumb ORDER BY id DESC) AS rn
        FROM product
        -- 自定义过滤条件可写在此处，例如 WHERE deviceId > 0
    ) A
    WHERE rn = 2
) s ON f.prodnumb = s.prodnumb;

逻辑说明 #

该方案通过两个子查询分别取每个产品的最新、次新记录，再通过产品编号关联拼接为单行，若某产品仅存在1条采集记录，次新日期、次新价格字段会返回NULL，符合业务预期。

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