Question

sql两个表连接查询，如何将列变成一行显示

有两个表，customer和phone，customer主键是customerid,phone主键是phoneid其结构如下
customer
customerid,customername,address
1 a lll
2 b ddd
phone
phoneid,customerid,phonenum
1 1 123
2 1 234
3 1 342
4 2 366
5 2 333
如何实现这样的查询结果
customerid,customername,address,phonenum1,phonenum2,phonenum3
1 a lll 123 234 342

Answer 1

select customer.customerid,customername,address,phoneid,phonenum1,phonenum2,phonenum3 from customer,phone where customer.customerid=phone.customerid and customer.customerid = 1;

注意：可能有字段名拼写错误，使用前请检查

Answer 2

select customerid,customername,address,p.phone from customer t left join (select customerid as id,GROUP_CONCAT(phonenum separator ',') phone from phone group by customerid) p on p.id = t.customerid;