Mysql如何从不同的列中选择值到彼此之间的单个列

Question

我有一张这样的 table :

+------+-------+
|yearIn|yearOut|
+------+-------+
|1974  |2012   |
+------+-------+
|1935  |2020   |
+------+-------+
|1980  |1999   |
+------+-------+

我需要选择所有这些年份并将它们放在一个列中，如下所示:

+------+
|years |
+------+
|1974  |
+------+
|1935  |
+------+
|1980  |
+------+
|2012  |
+------+
|2020  |
+------+
|1999  |
+------+

在此先感谢您的帮助:-)

Answer 1

你可以用这样的查询来完成:

SELECT Years
FROM (
    SELECT yearIn AS Years FROM yourTable
    UNION ALL 
    SELECT yearOut FROM yourTable
) y
ORDER BY Years;

您也可以在没有子查询的情况下使用它

SELECT yearIn AS Years FROM yourTable
UNION ALL 
SELECT yearOut FROM yourTable
ORDER BY Years;

EXPLAIN 带和不带子查询

mysql> EXPLAIN SELECT Years
    -> FROM (
    ->     SELECT yearIn AS Years FROM yourTable
    ->     UNION ALL
    ->     SELECT yearOut FROM yourTable
    -> ) y
    -> ORDER BY Years;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using filesort |
|  2 | DERIVED     | yourTable  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL           |
|  3 | UNION       | yourTable  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
3 rows in set, 1 warning (0,00 sec)

mysql>
mysql> EXPLAIN     SELECT yearIn AS Years FROM yourTable
    ->     UNION ALL
    ->     SELECT yearOut FROM yourTable
    ->     ORDER BY Years;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY      | yourTable  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                            |
|  2 | UNION        | yourTable  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary; Using filesort |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
3 rows in set, 1 warning (0,00 sec)

mysql>