mysql - SQL : splitting a row in two rows

Question

我有一个看起来像这样的表

|  ID  |   item1   |   item 2  | 
|   1  |     A1    |     B1    |
|   2  |     A2    |     B2    |

我想得到这样的东西:

|  ID  |   item  |
|   1  |    A1   |
|   1  |    B1   |
|   2  |    A2   |
|   2  |    B2   |

在某种意义上它与this SO question相反.

Answer 1

使用UNION ALL:

SELECT ID, item1 AS item
FROM mytable

UNION ALL

SELECT ID, item2 AS item
FROM mytable
ORDER BY ID, item