用上面的值填补空白

2skhul33 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(274)

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

+------------+------+------------+---------+
| 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列，以得到下面所需的结果。

+------------+------+------------+---------+
| 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

来源：https://stackoverflow.com/questions/53265300/mysql-5-5-fill-gaps-with-value-from-above

1条答案

按热度按时间

iq0todco1#

假设有一个主键列（假设 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视图

赞(0）回复(0）举报 2021-06-18

我来回答

用上面的值填补空白

1条答案

相关问题

热门标签

最新问答