在mysql查询中将第二个字段与第一个字段连接起来

I have a table with the below information. Keep in mind that the columns ID,Year,ID2 together creates a key.

我有一张包含以下信息的表格。请记住,列ID,年份,ID2一起创建一个键。

+----+------+-------+------+------+
| ID | Year | ID2   |Year1 |Year2 |
+----+------+-------+------+------+
|  1 | 1    | 12    |4     |      |
|  1 | 1    | 13    |6     |      |
|  1 | 1    | 22    |7     |      |
|  1 | 2    | 12    |      |4     |
|  1 | 2    | 15    |      |5     |
|  1 | 2    | 17    |      |4     |
|  1 | 2    | 25    |      |5     |
+----+------+-------+------+------+

I would like to concatenate the Year1 field all the way down when applicable and the same for Year2 field such as it will not show duplicates if a number is already there. I want the query to present the result below.

我想在适用时将Year1字段连接起来,而对于Year2字段则相同,例如,如果已有数字,则不会显示重复项。我希望查询在下面显示结果。

+----+------+-------+------+------+
| ID | Year | ID2   |Year1 |Year2 |
+----+------+-------+------+------+
|  1 | 1    | 12    |4     |      |
|  1 | 1    | 13    |4,6   |      |
|  1 | 1    | 22    |4,6,7 |      |
|  1 | 2    | 12    |      |4     |
|  1 | 2    | 15    |      |4,5   |
|  1 | 2    | 17    |      |4,5   |
|  1 | 2    | 25    |      |4,5   |
+----+------+-------+------+------+

Thanks in advance!

提前致谢!

1 个解决方案

#1

You can do a self-join on the same id and year and a smaller id2 use group_concat to concatenate the corresponding year1 and year2 columns

你可以在相同的id和year上进行自联接,而较小的id2使用group_concat来连接相应的year1和year2列

select t1.id, t1.year, t1.id2,
    group_concat(distinct t2.year1 order by t2.year) year1,
    group_concat(distinct t2.year2 order by t2.year2) year2
from mytable t1
join mytable t2 on t2.id = t1.id 
    and t2.year = t1.year 
    and t2.id2 <= t1.id2
group by t1.id, t1.year, t1.id2
order by t1.id, t1.year, t1.id2