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

Question

我在工作中有一个程序，可以输出到CSV，但在最敏感的地方留下空白。我想在同一行上查看运营商和目的地，当前运营商在目的地上方1行，如下所示：

我有一个数据库，如下所示：

|Key|Carrier ||Destination|

|-------------------------|

| 1 | HULL2 || |

| 2 | || C14A102 |

| 3 | DONC1 || |

| 4 | || D15A012 |

我想要的：

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

或者使用carrier列中的信息插入新列。

抱歉，如果这让我感到困惑，我很难解释它！

詹姆斯

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)

Answer 2

假设'key‘列可以以这种方式被信任，我将使用连接使用key = key+1的自连接进行更新，然后确保它只影响偶数行。

UPDATE tablename as even_row JOIN tablename as odd_row 
ON even_row.Key = odd_row.Key + 1 
SET even_row.Carrier = odd_row.Carrier 
WHERE odd_row.Key % 2;