将多个电话号码连接到列中:phone1, phone2, phone3

Question

我想将多个电话号码连接到一个客户，以获得完整的CSV导出，而不需要重复的条目。我的数据结构是:

客户

<表类>IDfirstnamelastnametbody><<tr>1约翰和2Max佩恩

我建议你把所有的电话放在一列:

select c.*, group_concat(p.number order by p.id) as numbers
from customer c left join
phones p
on c.id = p.customer_id
group by c.id;

(注意:这里假设customer(id)是customer的主键)

如果需要单独的列，可以使用条件聚合:

select c.*, 
max(case when seqnum = 1 then p.number end) as phone_1,
max(case when seqnum = 2 then p.number end) as phone_2,
. . .
from customer c left join
(select p.*,
row_number() over (partition by customer_id order by p.id) as seqnum
from phones p
) p
on c.id = p.customer_id
group by c.id;