我得到了这样的表格:
+------+-------+
|yearIn|yearOut|
+------+-------+
|1974 |2012 |
+------+-------+
|1935 |2020 |
+------+-------+
|1980 |1999 |
+------+-------+
我需要选择所有这些年份并将它们放在一列中,如下所示:
+------+
|years |
+------+
|1974 |
+------+
|1935 |
+------+
|1980 |
+------+
|2012 |
+------+
|2020 |
+------+
|1999 |
+------+
提前感谢任何帮助:-(
您可以使用如下查询来执行此操作:
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;
带子查询和不带子查询的解释
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>