时间戳

首页 > 解决方案 > MySQL 5.5 - 用上面的值填补空白

mysql - MySQL 5.5 - 用上面的值填补空白

问题描述

我有一个包含不同报告日期的全球销售数据的表格，如下所示：

+------------+------+------------+---------+
| Closed     | Open | Plan       | Station |
+------------+------+------------+---------+
| 2018-10-23 | NULL | NULL       | A       |
| 2018-10-22 | NULL | NULL       | NULL    |
| 2018-10-22 | NULL | NULL       | B       |
| 2018-10-22 | NULL | NULL       | NULL    |
| NULL       | NULL | 2018-10-23 | C       |
| NULL       | NULL | 2018-10-22 | NULL    |
| NULL       | NULL | 2018-10-22 | NULL    |
+------------+------+------------+---------+

CREATE TABLE Orders
(Closed DATE, 
Open DATE,
Plan DATE,
Station Char);


insert into Orders values ("2018-10-23",NULL,NULL, "A");    
insert into Orders values ("2018-10-22",NULL,NULL, NULL);    
insert into Orders values ("2018-10-22",NULL,NULL, "B");    
insert into Orders values ("2018-10-22",NULL,NULL, NULL);    
insert into Orders values (NULL,NULL,"2018-10-23", "C");
insert into Orders values (NULL,NULL,"2018-10-22", NULL);
insert into Orders values (NULL,NULL,"2018-10-22", NULL);

我想用最后一个知道的值填充 Station Column 以获得以下所需的结果。

+------------+------+------------+---------+
| Closed     | Open | Plan       | Station |
+------------+------+------------+---------+
| 2018-10-23 | NULL | NULL       | A       |
| 2018-10-22 | NULL | NULL       | A       |
| 2018-10-22 | NULL | NULL       | B       |
| 2018-10-22 | NULL | NULL       | B       |
| NULL       | NULL | 2018-10-23 | C       |
| NULL       | NULL | 2018-10-22 | C       |
| NULL       | NULL | 2018-10-22 | C       |
+------------+------+------------+---------+

标签： mysql

解决方案

假设id您的表中有一个主键列（比如说 ），它可用于定义 的“最后已知”值Station。请记住，数据是以无序方式存储的，如果不定义特定的顺序和/或主键，我们就无法真正定义“最后一个已知”的值。

Coalesce()函数将用于处理特定行中的null值。Station然后，我们可以使用相关子查询来确定“最后一个已知”的值。

SELECT 
  t1.Closed, 
  t1.Open, 
  t1.Plan, 
  COALESCE(t1.Station, 
           (SELECT t2.Station 
            FROM Orders AS t2 
            WHERE t2.id < t1.id 
              AND t2.Station IS NOT NULL 
            ORDER BY t2.id DESC 
            LIMIT 1)) AS Station 
FROM Orders AS t1

结果

| Closed     | Open | Plan       | Station |
| ---------- | ---- | ---------- | ------- |
| 2018-10-23 |      |            | A       |
| 2018-10-22 |      |            | A       |
| 2018-10-22 |      |            | B       |
| 2018-10-22 |      |            | B       |
|            |      | 2018-10-23 | C       |
|            |      | 2018-10-22 | C       |
|            |      | 2018-10-22 | C       |

在 DB Fiddle 上查看

推荐阅读