MYSQL:如何用前一个条目填充列中的空值？

Question

我有一个程序可以导出为 CSV，但会在最烦人的地方留下空白。我想在同一行查看承运人和目的地，目前承运人在目的地上方 1 行，如下所示:

我有一个类似下面的数据库:
|Key|Carrier ||Destination|
|------------------------|
| 1 |船体2 || |
| 2 | || C14A102 |
| 3 | DONC1 || |
| 4 | || D15A012 |

我想要的:

 |Key|Carrier ||Destination|
 |-------------------------|
 | 1 | HULL2  ||           |
 | 2 | HULL2  ||  C14A102  |
 | 3 | DONC1  ||           |
 | 4 | DONC1  ||  D15A012  |

或者使用运营商列中的信息插入一个新列。

抱歉，如果这让我感到困惑，我无法解释它!

詹姆斯

Answer 1

这是一个解决方案，通过克隆另一个表然后将其删除:

CREATE TABLE t1(Key_id INT PRIMARY KEY, Carrier CHAR(20), Destination CHAR(20));
INSERT INTO t1 VALUES(1, 'HULL2', ''),(2,'','C14A102'),(3,'DONC1',''),(4,'','D15A012');

CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT * FROM t1;

SELECT * FROM t1;
UPDATE t1 SET Carrier = 
    (
    SELECT t2.Carrier
    FROM t2
    WHERE t2.Key_id < t1.Key_id AND t2.Carrier != ''
    ORDER BY t2.Key_id DESC
    LIMIT 1
    )
WHERE Carrier = '';
SELECT * FROM t1;

DROP TABLE t2;

输出:

mysql> SELECT * FROM t1;
+--------+---------+-------------+
| Key_id | Carrier | Destination |
+--------+---------+-------------+
|      1 | HULL2   |             |
|      2 |         | C14A102     |
|      3 | DONC1   |             |
|      4 |         | D15A012     |
+--------+---------+-------------+
4 rows in set (0.00 sec)

mysql> UPDATE t1 SET Carrier =
    -> (
    -> SELECT t2.Carrier
    -> FROM t2
    -> WHERE t2.Key_id < t1.Key_id AND t2.Carrier != ''
    -> ORDER BY t2.Key_id DESC
    -> LIMIT 1
    -> )
    -> WHERE Carrier = '';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM t1;
+--------+---------+-------------+
| Key_id | Carrier | Destination |
+--------+---------+-------------+
|      1 | HULL2   |             |
|      2 | HULL2   | C14A102     |
|      3 | DONC1   |             |
|      4 | DONC1   | D15A012     |
+--------+---------+-------------+
4 rows in set (0.00 sec)