今天这道题有点简单，来自公众号的题：mp.weixin.qq.com/s/poor6zDHI…

题干

测试数据

CREATE TABLE G1108(item VARCHAR(10),buydate DATE,price INT);
INSERT INTO G1108 VALUES('A','2021/11/1',100),('A','2021/10/1',120),('A','2021/12/1',115),('B','2021/11/1',99),('B','2021/9/1',88);

解题

这里需要按照购买日期降序排序，用row_number辅助转列。比较简单就不废话了。

PS，这里分享一下行列互换的博客文章：juejin.cn/post/702993…

with tmp as (
select
    item,
    buydate,
    price,
    row_number() over(partition by item order by buydate desc) as rn
from 
    G1108)

select
    tba.item as `物品`,
    ifnull(tba.price, 0) as `最新单价`,
    ifnull(tbb.price, 0) as `上一次单价`,
    ifnull(tbc.price, 0) as `上上次单价`
from 
    (select item, price, rn from tmp where rn=1) as tba
left outer join
    (select item, price, rn from tmp where rn=2) as tbb
on tba.item=tbb.item 
left outer join
    (select item, price, rn from tmp where rn=3) as tbc
on tba.item=tbc.item 
;