MySQL实战--＞返回最近一次的数据

一、场景说明

1）某超市业务情况：某一超市不定期更新商品货源状态，但后台只记录该货品更新货源状态时间点的信息（详见下图一），未更新时后台该字段为null（后台数据库每日自动记录货源信息），现为方便查询某商品在某时间下的货源状态，对货源状态进行梳理，对记录为null的数据进行补充反馈最近一次的数据

2）商品货源状态表信息：

id	time	commodity	status
1001	2023/3/1	洗发水	上架
1002	2023/3/1	沐浴乳	售罄
1003	2023/3/1	肥皂	上架
1004	2023/3/1	花露水	上架
1005	2023/3/1	水桶	上架
1001	2023/3/2	洗发水
1002	2023/3/2	沐浴乳	下架
1003	2023/3/2	肥皂
1004	2023/3/2	花露水
1005	2023/3/2	水桶
1001	2023/3/3	洗发水	下架
1002	2023/3/3	沐浴乳
1003	2023/3/3	肥皂
1004	2023/3/3	花露水	下架
1005	2023/3/3	水桶
1001	2023/3/4	洗发水
1002	2023/3/4	沐浴乳
1003	2023/3/4	肥皂
1004	2023/3/4	花露水
1005	2023/3/4	水桶

 3）商品货源状态表梳理效果展示：

id	time	commodity	status	梳理
1001	2023/3/1	洗发水	上架	上架
1002	2023/3/1	沐浴乳	售罄	售罄
1003	2023/3/1	肥皂	上架	上架
1004	2023/3/1	花露水	上架	上架
1005	2023/3/1	水桶	上架	上架
1001	2023/3/2	洗发水		上架
1002	2023/3/2	沐浴乳	下架	下架
1003	2023/3/2	肥皂		上架
1004	2023/3/2	花露水		上架
1005	2023/3/2	水桶		上架
1001	2023/3/3	洗发水	下架	下架
1002	2023/3/3	沐浴乳		下架
1003	2023/3/3	肥皂		上架
1004	2023/3/3	花露水	下架	下架
1005	2023/3/3	水桶		上架
1001	2023/3/4	洗发水		下架
1002	2023/3/4	沐浴乳		下架
1003	2023/3/4	肥皂		上架
1004	2023/3/4	花露水		下架
1005	2023/3/4	水桶		上架

 二、参考答案及效果演示：

-- 法一：
SELECT 
			a.*,
			(select a1.type
			 from (SELECT * FROM `20230314` where type!='') a1
			 where a.id=a1.id
			 and a.time>=a1.time
			 ORDER BY a1.time desc LIMIT 1) as 梳理
from `20230314` a
ORDER BY id,time,commodity,type;



-- 法二：
SELECT 
 id,time,commodity,type,
 SUBSTRING_INDEX(GROUP_CONCAT(type1 ORDER BY id,time,time1 ),',',-1 ) as 梳理
 FROM
 (SELECT
  a.*,
  a1.id AS id1,
  a1.time AS time1,
  a1.type AS type1 
 FROM `20230314` a 
 left JOIN ( SELECT * FROM `20230314` WHERE type !='') a1 on a.time >= a1.time
 AND a.id = a1.id 
 ) a1 
GROUP BY id,time,commodity,type
ORDER BY id,time,commodity,type;

三、示例表数据创建

drop table if exists `20230314`;
CREATE TABLE `20230314`  (
  `id` int NOT null,
  `time` datetime NOT null,
  `commodity` varchar(32) NOT null,
  `type` varchar(32)
) ;
INSERT INTO `20230314` VALUES ('1001','2023/03/01','洗发水','上架');
INSERT INTO `20230314` VALUES ('1002','2023/03/01','沐浴乳','售罄');
INSERT INTO `20230314` VALUES ('1003','2023/03/01','肥皂','上架');
INSERT INTO `20230314` VALUES ('1004','2023/03/01','花露水','上架');
INSERT INTO `20230314` VALUES ('1005','2023/03/01','水桶','上架');
INSERT INTO `20230314` VALUES ('1001','2023/03/02','洗发水','');
INSERT INTO `20230314` VALUES ('1002','2023/03/02','沐浴乳','下架');
INSERT INTO `20230314` VALUES ('1003','2023/03/02','肥皂','');
INSERT INTO `20230314` VALUES ('1004','2023/03/02','花露水','');
INSERT INTO `20230314` VALUES ('1005','2023/03/02','水桶','');
INSERT INTO `20230314` VALUES ('1001','2023/03/03','洗发水','下架');
INSERT INTO `20230314` VALUES ('1002','2023/03/03','沐浴乳','');
INSERT INTO `20230314` VALUES ('1003','2023/03/03','肥皂','');
INSERT INTO `20230314` VALUES ('1004','2023/03/03','花露水','下架');
INSERT INTO `20230314` VALUES ('1005','2023/03/03','水桶','');
INSERT INTO `20230314` VALUES ('1001','2023/03/04','洗发水','');
INSERT INTO `20230314` VALUES ('1002','2023/03/04','沐浴乳','');
INSERT INTO `20230314` VALUES ('1003','2023/03/04','肥皂','');
INSERT INTO `20230314` VALUES ('1004','2023/03/04','花露水','');
INSERT INTO `20230314` VALUES ('1005','2023/03/04','水桶','');