SQL查询商品最近十二次报价

需求

数据库存的是单个物料、单个公司单次报价的数据，现在需要做一个报价的折线图用来查看物料的报价趋势，也就是按照时间倒序，取前面十二条数据即可

方案1

WITH temp AS (
    SELECT 
        wlbm,
        gs,
        ROW_NUMBER() OVER (PARTITION BY wlbm, gs ORDER BY cjsj DESC) AS rn,
        hsdj 
    FROM 
        uf_price_lib 
)
SELECT
    ROW_NUMBER() OVER (ORDER BY wlbm) AS id,
    wlbm,
    gs,
    MAX(CASE WHEN rn = 1 THEN hsdj END) AS hsdj_1,
    MAX(CASE WHEN rn = 2 THEN hsdj END) AS hsdj_2,
    MAX(CASE WHEN rn = 3 THEN hsdj END) AS hsdj_3,
    MAX(CASE WHEN rn = 4 THEN hsdj END) AS hsdj_4,
    MAX(CASE WHEN rn = 5 THEN hsdj END) AS hsdj_5,
    MAX(CASE WHEN rn = 6 THEN hsdj END) AS hsdj_6,
    MAX(CASE WHEN rn = 7 THEN hsdj END) AS hsdj_7,
    MAX(CASE WHEN rn = 8 THEN hsdj END) AS hsdj_8,
    MAX(CASE WHEN rn = 9 THEN hsdj END) AS hsdj_9,
    MAX(CASE WHEN rn = 10 THEN hsdj END) AS hsdj_10,
    MAX(CASE WHEN rn = 11 THEN hsdj END) AS hsdj_11,
    MAX(CASE WHEN rn = 12 THEN hsdj END) AS hsdj_12 
FROM 
    temp 
GROUP BY 
    wlbm, gs;

上述查询主要使用了以下MySQL特性和功能：

1. 公共表表达式（CTE）：使用 WITH 语法。
2. 窗口函数：使用 ROW_NUMBER() 进行行编号。
3. 条件聚合：使用 MAX(CASE WHEN … THEN … END) 来实现条件列的选择。

此方案hsdj_1即最近一次报价，hsdj_12即最近第十二次报价。如果需要将顺序反转，也就是将CASE WHEN rn = 12 THEN hsdj END 中rn的值替换，但是这样又会有一个问题，如果这个物料没有十二次报价，此时hsdj_1则为NULL，不符合需求，不满十二条报价，则应该由12往前移，而不是替换rn的值，那么就需要用到窗口函数，如方案2

方案2

WITH temp AS (
    SELECT 
        wlbm,
        gs,
        ROW_NUMBER() OVER (PARTITION BY wlbm, gs ORDER BY cjsj DESC) AS rn,
        COUNT(*) OVER (PARTITION BY wlbm, gs) AS total_count,
        hsdj
    FROM 
        uf_price_lib 
),
numbered_prices AS (
    SELECT
        wlbm,
        gs,
        rn,
        total_count,
        hsdj,
        CASE 
            WHEN total_count >= 12 THEN rn
            ELSE total_count - rn + 1
        END AS adjusted_rn
    FROM 
        temp
)
SELECT
    ROW_NUMBER() OVER (ORDER BY wlbm) AS id,
    wlbm,
    gs,
    MAX(CASE WHEN adjusted_rn = 1 THEN hsdj END) AS hsdj_1,
    MAX(CASE WHEN adjusted_rn = 2 THEN hsdj END) AS hsdj_2,
    MAX(CASE WHEN adjusted_rn = 3 THEN hsdj END) AS hsdj_3,
    MAX(CASE WHEN adjusted_rn = 4 THEN hsdj END) AS hsdj_4,
    MAX(CASE WHEN adjusted_rn = 5 THEN hsdj END) AS hsdj_5,
    MAX(CASE WHEN adjusted_rn = 6 THEN hsdj END) AS hsdj_6,
    MAX(CASE WHEN adjusted_rn = 7 THEN hsdj END) AS hsdj_7,
    MAX(CASE WHEN adjusted_rn = 8 THEN hsdj END) AS hsdj_8,
    MAX(CASE WHEN adjusted_rn = 9 THEN hsdj END) AS hsdj_9,
    MAX(CASE WHEN adjusted_rn = 10 THEN hsdj END) AS hsdj_10,
    MAX(CASE WHEN adjusted_rn = 11 THEN hsdj END) AS hsdj_11,
    MAX(CASE WHEN adjusted_rn = 12 THEN hsdj END) AS hsdj_12
FROM 
    numbered_prices
GROUP BY 
    wlbm, gs;

执行以上sql效果如图

以物料编码 35284，公司 8数据为例

总共有十条报价，最近一次报价为32，那么hsdj_10则为32，hsdj_1则为29