mysql每日一题0708--- 临近值补全数据

onemorepoint

于 2021-07-27 19:08:00 发布

阅读量148

点赞数

分类专栏： mysql每日一题文章标签： mysql java sql 数据库 android

本文链接：https://blog.youkuaiyun.com/onemorepoint/article/details/119164020

版权

mysql每日一题专栏收录该内容

12 篇文章

订阅专栏

测试数据

CREATE TABLE T0708 
(LDate DATE NOT NULL,
Value1 INT NULL,
Value2 INT NULL
)
INSERT INTO T0708 VALUES('2020-11-25', 500 ,200);
INSERT INTO T0708 VALUES('2020-11-24', Null, 200);
INSERT INTO T0708 VALUES('2020-11-23', Null, 250);
INSERT INTO T0708 VALUES('2020-11-22', 300 ,Null);

solution1

SELECT 
T1.LDATE,
CASE 
	WHEN VALUE1 IS NULL THEN (SELECT  VALUE1 FROM T0708 T2 WHERE T2.LDATE < T1.LDATE AND VALUE1 IS NOT NULL ORDER BY LDATE DESC limit 1 )
	ELSE VALUE1
END AS VALUE1,
CASE 
	WHEN VALUE2 IS NULL THEN (SELECT  VALUE2 FROM T0708 T2 WHERE T2.LDATE < T1.LDATE AND VALUE2 IS NOT NULL ORDER BY LDATE DESC limit 1)
	ELSE VALUE2
END AS VALUE2
FROM T0708 T1