我实际上需要用存档中的下一个可用数据填充不存在的条目。

背景:因此,我有一条记录,其中记录了每个月的每个状态。但是,它仅在有新值时更新,并将旧值保存到上个月。使用MySQL v8。

如何在下一个填写的时间段内使用具有相同ID的数据填写表中的缺失值?

带有archivesDate='2019-08-01'的所有内容都故意在其中包含NULL数据。这意味着下个月是数据开始的时间。

将来需要使用带有identtID的下一个数据填充带有archivesDate='2019-09-01'的中间3个数据;在这种情况下,它来自archivesDate='2019-10-01'。可能是10月,11月或将来的任何时候。它只需要填写下一个值即可。

我尝试过的,但这只是ID上的一个联接,这就是为什么9月显示为NULL的原因。

SELECT
    al.statusID,
    sa.statusDate,
    sa.statusFlagsID,
    sa.statusPrice,
    al.archivesDate
FROM (
    SELECT
        sid.statusID AS statusID,
        a.archivesID AS archivesID,
        a.archivesDate AS archivesDate
    FROM (
            SELECT
                DISTINCT statusID
            FROM statusArchive) sid
            CROSS JOIN archives a) al
LEFT JOIN statusArchive sa
    ON sa.statusID = al.statusID --This needs to be changed to get next value in cases where there is no value available
        AND sa.archivesID = al.archivesID --This needs to be changed to get next value  in cases where there is no value available
ORDER BY al.archivesDate, al.statusID

当前输出:
statusID statusDate statusFlagsID statusPrice archivesDate
1                                              2019-08-01
2                                              2019-08-01
3                                              2019-08-01
1                                              2019-09-01
2                                              2019-09-01
3                                              2019-09-01
1        2018-10-01  1            37           2019-10-01
2        2018-11-05  1            90           2019-10-01
3        2019-01-01  1            3            2019-10-01

所需的输出:
statusID statusDate statusFlagsID statusPrice archivesDate
1                                              2019-08-01
2                                              2019-08-01
3                                              2019-08-01
1        2018-10-01  1            37           2019-09-01
2        2018-11-05  1            90           2019-09-01
3        2019-01-01  1            3            2019-09-01
1        2018-10-01  1            37           2019-10-01
2        2018-11-05  1            90           2019-10-01
3        2019-01-01  1            3            2019-10-01

编辑:添加了模型源表(提供上述所有信息):

源数据

档案
archivesID       archivesDate
1                2019-08-01
2                2019-09-01
3                2019-10-01

STATUSARCHIVE(将s替换为以下列名称中的状态,抱歉,格式化问题)

注意:archivesID=2中缺少条目
sArchiveID  sID  sFlagsID  sPrice  sDate       archivesID
1           1    NULL      NULL    NULL        1
2           2    NULL      NULL    NULL        1
3           3    NULL      NULL    NULL        1
4           1    1         37      2018-10-01  3
5           2    1         90      2018-11-05  3
6           3    1         3       2019-01-01  3

并使用 DB-Fiddle here和SQL创建数据源:
CREATE TEMPORARY TABLE archives (
  archivesID INT,
  archivesDate DATE
);
INSERT INTO archives (archivesID, archivesDate) VALUES (1, '2019-08-01');
INSERT INTO archives (archivesID, archivesDate) VALUES (2, '2019-09-01');
INSERT INTO archives (archivesID, archivesDate) VALUES (3, '2019-10-01');

CREATE TEMPORARY TABLE statusArchive (
  statusArchiveID INT,
  statusID INT,
  statusFlagsID INT,
  statusPrice DECIMAL,
  statusDate DATE,
  archivesID INT
);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID)
    VALUES (1, 1, NULL, NULL, NULL, 1);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID)
    VALUES (2, 2, NULL, NULL, NULL, 1);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID)
    VALUES (3, 3, NULL, NULL, NULL, 1);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID)
    VALUES (4, 1, 1, 37, '2018-10-01', 3);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID)
    VALUES (5, 2, 1, 90, '2018-11-05', 3);
INSERT INTO statusArchive (statusArchiveID, statusID, statusFlagsID, statusPrice, statusDate, archivesID)
    VALUES (6, 3, 1, 3, '2019-01-01', 3);

最佳答案

快速解决方案:将您的注释转换为代码。

AND sa.archivesID = al.archivesID -- This needs to be changed to get next value
                                  -- in cases where there is no value available

变成
AND sa.archivesID = (
    SELECT MIN(x.archivesID)
    FROM statusArchive x
    WHERE x.statusID = al.statusID
      AND x.archivesID >= al.archivesID
)

完整查询:
SELECT
    al.statusID,
    sa.statusDate,
    sa.statusFlagsID,
    sa.statusPrice,
    al.archivesDate
FROM (
    SELECT
        sid.statusID AS statusID,
        a.archivesID AS archivesID,
        a.archivesDate AS archivesDate
    FROM (SELECT DISTINCT statusID FROM statusArchive) sid
    CROSS JOIN archives a
) al
LEFT JOIN statusArchive sa
    ON  sa.statusID = al.statusID
    AND sa.archivesID = (
        SELECT MIN(x.archivesID)
        FROM statusArchive x
        WHERE x.statusID = al.statusID
          AND x.archivesID >= al.archivesID
    )
ORDER BY al.archivesDate, al.statusID

结果:
| statusID | archivesDate | statusDate | statusFlagsID | statusPrice |
| -------- | ------------ | ---------- | ------------- | ----------- |
| 1        | 2019-08-01   |            |               |             |
| 2        | 2019-08-01   |            |               |             |
| 3        | 2019-08-01   |            |               |             |
| 1        | 2019-09-01   | 2018-10-01 | 1             | 37          |
| 2        | 2019-09-01   | 2018-11-05 | 1             | 90          |
| 3        | 2019-09-01   | 2019-01-01 | 1             | 3           |
| 1        | 2019-10-01   | 2018-10-01 | 1             | 37          |
| 2        | 2019-10-01   | 2018-11-05 | 1             | 90          |
| 3        | 2019-10-01   | 2019-01-01 | 1             | 3           |

View on DB Fiddle

注意:您应该在statusArchive(statusID, archivesID [other columns])上有一个索引。

以下(简短的)查询也适用于给定的样本数据:
select
  s.statusID,
  s.statusDate,
  s.statusFlagsID,
  s.statusPrice,
  a.archivesDate
from archives a
left join statusArchive s
  on s.archivesID = (
    select min(x.archivesID)
    from statusArchive x
    where x.archivesID >= a.archivesID
  )
order by a.archivesDate, s.statusID;

对于此查询,您应该在statusArchive(archivesID)上有一个索引。

结果:
| statusID | statusDate | statusFlagsID | statusPrice | archivesDate |
| -------- | ---------- | ------------- | ----------- | ------------ |
| 1        |            |               |             | 2019-08-01   |
| 2        |            |               |             | 2019-08-01   |
| 3        |            |               |             | 2019-08-01   |
| 1        | 2018-10-01 | 1             | 37          | 2019-09-01   |
| 2        | 2018-11-05 | 1             | 90          | 2019-09-01   |
| 3        | 2019-01-01 | 1             | 3           | 2019-09-01   |
| 1        | 2018-10-01 | 1             | 37          | 2019-10-01   |
| 2        | 2018-11-05 | 1             | 90          | 2019-10-01   |
| 3        | 2019-01-01 | 1             | 3           | 2019-10-01   |

View on DB Fiddle

但是-我不知道这是否会在任何可能的数据集上返回期望的结果,因为我只能从样本数据和结果中猜测确切的要求。

更新资料

如果需要由相应的archivesDate排序的下一行,则需要一个ORDER BY ... LIMIT 1子查询:
SELECT
    al.statusID,
    sa.statusDate,
    sa.statusFlagsID,
    sa.statusPrice,
    al.archivesDate
FROM (
    SELECT
        sid.statusID AS statusID,
        a.archivesID AS archivesID,
        a.archivesDate AS archivesDate
    FROM (SELECT DISTINCT statusID FROM statusArchive) sid
    CROSS JOIN archives a
) al
LEFT JOIN statusArchive sa
    ON  sa.statusID = al.statusID
    AND sa.archivesID = (
        SELECT x.archivesID
        FROM statusArchive x
        JOIN archives y ON y.archivesID = x.archivesID
        WHERE x.statusID = al.statusID
          AND y.archivesDate >= al.archivesDate
        ORDER BY y.archivesDate ASC
        LIMIT 1
    )
ORDER BY al.archivesDate, al.statusID;

确保您有索引
  • statusArchive(statusID [, other columns])以及x.statusID = al.statusID
  • SELECT DISTINCT statusID FROM statusArchive条件的
  • archives(archivesID, archivesDate, [, other columns])以及ORDER BY子句y.archivesID = x.archivesID
  • 08-07 00:11