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

Question

我有一张包含以下信息的表格。请记住，ID、Year、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     |
+----+------+-------+------+------+

我想在适用时将 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   |
+----+------+-------+------+------+

提前致谢!

Answer 1

您可以在相同的 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