mysql - 从一列中选择电话号码到多列sql

Question

我有一个名为“phones”的表，我需要返回前三个电话号码，并在六个单独的列中返回每个 c_no 的三种类型，然后加入到联系人表中。电话 table 是这样布置的，

p_no,c_no, phone, p_type

我想要的结果集是，

c_no, First_Phone, First_Phone_Type, Second_Phone, Second_Phone_Type, Third_Phone, Third_Phone_Type

我尝试了以下操作，但当我按 c_no 添加组时，它在第二列或第三列中没有显示任何内容

select  c_no,
        (case when cp_sort = 1 and cp_status = 1 then phone end) as Phone_1,
        (case when cp_sort = 2 and cp_status = 1 then phone end) as Phone_2,
        (case when cp_sort = 3 and cp_status = 1 then phone end) as Phone_3
 from   phones cp  
 group by
         c_no

提前非常感谢大家的帮助!

Answer 1

你可以尝试:

select distinct c_no, 
  (select phone from phones p1 where p0.c_no = p1.c_no and cp_sort = 1) Phone_1,
  (select phone from phones p2 where p0.c_no = p2.c_no and cp_sort = 2) Phone_2,
  (select phone from phones p3 where p0.c_no = p3.c_no and cp_sort = 3) Phone_3
from phones p0;

可能需要一些优化才能在大量数据上正常工作。