MySQL:查询以返回同一表中的某些列+concat列值

Question

我有TableContacts:

ID Gender Person PhoneNumber
1  Male   Jhon   500000001
2  Male   Jhon   500000002
3  Male   Sam    500000003
4  Male   Sam    500000009

我会选择性别，ID=1的人，并将所有John电话号码连接在同一字段中，如下所示：

Gender Person PhoneNumbrSSS
Male   John   500000001, 50000002

感谢

Answer 1

使用查询返回Person和id = 1，然后使用group_concat()获取人员的电话号码：

select Gender, Person, 
group_concat(PhoneNumber) PhoneNumbrSSS
from TableContacts
where Person = (select Person from TableContacts where id = 1)
group by Gender, Person

请参阅演示
结果：

| Gender | Person | PhoneNumbrSSS       |
| ------ | ------ | ------------------- |
| Male   | Jhon   | 500000001,500000002 |

Answer 2

您可以使用GROUP_CONCT函数

SELECT Gender, Person, GROUP_CONCAT(PhoneNumber)
as "Phonenumber"  
from employee
where id=1
group by Gender, Person;

Answer 3

试试这个。

SELECT a.Gender, a.Person, GROUP_CONCAT(a.PhoneNumber)
as "Phonenumber"  
from employee a inner join employee b
on a.Person = b.Person
where a.id=1
group by a.Gender, a.Person;