mysql - 如何从 mySQL 中获取所有年份的列表

Question

+------------+---------------+------------+
| date       | other_columns | date2      |
+------------+---------------+------------+
| 2019-05-23 | #             | 2018-04-12 |
+------------+---------------+------------+
| 2013-04-08 | #             | null       |
+------------+---------------+------------+
| 2007-11-11 | #             | 2019-09-13 |
+------------+---------------+------------+ 

mySQL中有这样一个表，有几千行。我怎么能在不要求整个基地的情况下离开那里呢？我只是不知道该怎么做。

[2019,
2018,
2013,
2007]

我想提取像这样的东西，我很乐意提供任何建议

Answer 1

您可以使用year()和union all:

select distinct year(date)
from (select date from t union all
      select date2 from t
     ) t;

如果您希望将其作为单个连接字符串，我建议:

select group_concat(yyyy order by yyyy) as years
from (select year(date) as yyyy from t
      union -- intentionally remove duplicates
      select year(date2) from t
     ) t;