mysql - 如何从一行中的字段创建单独的一行？

我有一个这样的表：

// mytable
+----+--------+--------+
| id |  col1  |  col2  |
+----+--------+--------+
| 1  | one    | two    |
| 2  | three  | four   |
| 3  | five   | six    |
+----+--------+--------+

现在我要这张桌子：

// newmytable
+----+-------+---------+
| id |  col  | related |
+----+-------+---------+
| 1  | one   | 1       |
| 2  | two   | 1       |
| 3  | three | 2       |
| 4  | four  | 2       |
| 5  | five  | 3       |
| 6  | six   | 3       |
+----+-------+---------+

如果没有这样的related列，我可以这样做：

CREATE TABLE newmytable (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    SELECT col1 AS col FROM mytable
        UNION ALL
    SELECT col2  AS col FROM mytable
)

但是现在我想知道，我该怎么做呢？

最佳答案

您可以使用id来填充related列，如下所示：

CREATE TABLE newmytable (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY)
    SELECT col, related FROM (
           SELECT col1 AS col, id as related, 0 sort FROM mytable
               UNION ALL
           SELECT col2 AS col, id as related, 1 sort FROM mytable
    ) x
       ORDER BY x.related,x.sort ASC

Demo

关于mysql - 如何从一行中的字段创建单独的一行？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34680907/

Mytable

mysql - 如何从一行中的字段创建单独的一行？