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

image

测试数据


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



posted on 2021-07-27 19:08  多一点  阅读(48)  评论(0编辑  收藏  举报

导航