Mysql刷题笔记 -- 12.31 如果某列的字段在该日期为空值,查询时结果显示为之前最接近日期的非空值。 CREATE TABLE T1231 ( LDate DATE NOT NULL, Value1 INT NULL, Value2 INT NULL ); INSERT INTO T1231 VALUES('2020-11-25', 500 ,200); INSERT INTO T1231 VALUES('2020-11-24', Null, 200); INSERT INTO T1231 VALUES('2020-11-23', Null, 250); INSERT INTO T1231 VALUES('2020-11-22', 300 ,Null); INSERT INTO T1231 VALUES('2020-11-21', 200 ,320); T1231 +------------+--------+--------+ | LDate | Value1 | Value2 | +------------+--------+--------+ | 2020-11-25 | 500 | 200 | | 2020-11-24 | NULL | 200 | | 2020-11-23 | NULL | 250 | | 2020-11-22 | 300 | NULL | | 2020-11-21 | 200 | 320 | +------------+--------+--------+ 结果 +------------+--------+--------+ | LDate | VALUE1 | VALUE2 | +------------+--------+--------+ | 2020-11-25 | 500 | 200 | | 2020-11-24 | 300 | 200 | | 2020-11-23 | 300 | 250 | | 2020-11-22 | 300 | 320 | | 2020-11-21 | 200 | 320 | +------------+--------+--------+ SELECT A.LDate, ( SELECT B.Value1 FROM T1231 B WHERE B.LDate <= A.LDate AND B.Value1 IS NOT NULL ORDER BY A.LDate ASC LIMIT 1 ) AS VALUE1, ( SELECT B.Value2 FROM T1231 B WHERE B.LDate <= A.LDate AND B.Value2 IS NOT NULL ORDER BY A.LDate ASC LIMIT 1 ) AS VALUE2 FROM T1231 A