时间戳

首页 > 解决方案 > 如何在 MySQL 中查找和插入丢失的数据而不创建重复数据

mysql - 如何在 MySQL 中查找和插入丢失的数据而不创建重复数据

问题描述

我有一个问题，我有一个包含几个每日指标的表格，我每天都会在其中发布有关它们的信息，但是本月出现错误，这些指标中缺少某些日期，随机如下图所示：

select distinct fk_indicador_id, datareferencia, dataindicador FROM appweb_ind_exeindicadores where datareferencia = "2021-09-01" and fk_indicador_id = "61";

61  2021-09-01  2021-09-01
61  2021-09-01  2021-09-02
61  2021-09-01  2021-09-03
61  2021-09-01  2021-09-04
61  2021-09-01  2021-09-05
61  2021-09-01  2021-09-06
61  2021-09-01  2021-09-07
61  2021-09-01  2021-09-08
61  2021-09-01  2021-09-09
61  2021-09-01  2021-09-10
61  2021-09-01  2021-09-12
61  2021-09-01  2021-09-13
61  2021-09-01  2021-09-15
61  2021-09-01  2021-09-16
61  2021-09-01  2021-09-17
61  2021-09-01  2021-09-18
61  2021-09-01  2021-09-19
61  2021-09-01  2021-09-20
61  2021-09-01  2021-09-21
61  2021-09-01  2021-09-22
61  2021-09-01  2021-09-23
61  2021-09-01  2021-09-24
61  2021-09-01  2021-09-25
61  2021-09-01  2021-09-26
61  2021-09-01  2021-09-27
61  2021-09-01  2021-09-28
61  2021-09-01  2021-09-29
61  2021-09-01  2021-09-30

如何在不重复的情况下在整个表格中插入缺失的日期？

标签： mysqlsql

解决方案

您可以将插入选择与递归查询一起使用。递归查询为您获取所有指标的所有日期。然后插入表中缺少的那些。

insert into appweb_ind_exeindicadores (fk_indicador_id, dataindicador, datareferencia)
with recursive all_dates (fk_indicador_id, dataindicador, max_dataindicador) as
(
  select fk_indicador_id, min(dataindicador), max(dataindicador)
  from appweb_ind_exeindicadores
  group by fk_indicador_id
  union all
  select fk_indicador_id, dataindicador + interval 1 day, max_dataindicador
  from all_dates
  where dataindicador < max_dataindicador
)
select
  fk_indicador_id,
  dataindicador,
  dataindicador - interval (day(dataindicador) - 1) day as datareferencia
from all_dates
where (fk_indicador_id, dataindicador) not in
(
  select fk_indicador_id, dataindicador from appweb_ind_exeindicadores
);

推荐阅读