mysql - 带有相关列 ID 的分隔列表

Question

让我们假设下表:

| id         | firstname   | secondname   | group        |
|:-----------|------------:|:------------:|:------------:|
| 1          | Tom         | Test1        | 111          |
| 2          | Jack        | Test2        | 222          |
| 3          | Chris       | Test3        | 333          |
| 4          | Lucy        | Test4        | 333          |
| 5          | Joe         | Test5        | 111          |
| 6          | John        | Test6        | 111          |

我如何获取同一组中其他人的信息查询？

我想要得到这样的东西:

| id         | firstname   | secondname   | group        | others       |
|:-----------|------------:|:------------:|:------------:|:------------:|
| 1          | Tom         | Test1        | 111          | 5,6          |
| 2          | Jack        | Test2        | 222          |              |
| 3          | Chris       | Test3        | 333          | 4            |
| 4          | Lucy        | Test4        | 333          | 3            |
| 5          | Joe         | Test5        | 111          | 1,6          |
| 6          | John        | Test6        | 111          | 1,5          |

如果分离的列表也包含相关的 id 本身，那就没问题了。

我尝试了这个，但我只得到一行，其中所有 id 都在“其他”列中

Select A.id,A.firstname,A.secondname,A.group,GROUP_CONCAT(B.id) from myTable A
        inner join myTable B
        on (A.group = B.group)
        where a.id <> b.id 

Answer 1

您可以使用相关子查询:

select a.*,
       (select group_concat(b.id)
        from mytable b
        where b.group = a.group and b.id <> a.id
       ) as OthersInGroup
from myTable a;

注意:group是MySQL中的关键字和保留字，所以我希望这不是列的真实名称。

相关子查询似乎比左连接和聚合更简单。通过 mytable(group, id) 上的索引，它也应该具有相当好的性能。