如何将多行组合成一行？ SQL PostgreSQL管理员

Question

我已经根据公共ID映射了两个表，但我希望将所有与特定公司关联的电话号码放在一行而不是多行中，分为三列“电话”，“传真” ，和“免费电话”。以下是数据输出的示例。

Dr. Dimes 5553456879 Telephone 
Dr. Dimes 5553455600 Toll Free 
Dr. Dimes 555345Fax 

SELECT 
    fleet.company_name, 
    phone_number.phone_number, 
    phone_type.name 
FROM 
    fleetseek.phone_number, 
    fleetseek.phone_type, 
    fleetseek.fleet, 
    fleetseek.fleet_phone 
WHERE 
    phone_number.phone_type_id = phone_type.phone_type_id AND 
    fleet.fleet_id = fleet_phone.fleet_id AND 
    fleet_phone.phone_number_id = phone_number.phone_number_id 

Answer 1

也许是最简单的方式做，这是使用聚合：

SELECT 
    fleet.company_name, 
    MAX(CASE WHEN phone_type.name = 'Telephone' THEN phone_number.phone_number END) AS telephone, 
    MAX(CASE WHEN phone_type.name = 'Toll Free' THEN phone_number.phone_number END) AS toll_free, 
    MAX(CASE WHEN phone_type.name = 'Fax' THEN phone_number.phone_number END) AS fax 
FROM 
    fleetseek.phone_number, 
    fleetseek.phone_type, 
    fleetseek.fleet, 
    fleetseek.fleet_phone 
WHERE 
    phone_number.phone_type_id = phone_type.phone_type_id AND 
    fleet.fleet_id = fleet_phone.fleet_id AND 
    fleet_phone.phone_number_id = phone_number.phone_number_id 
GROUP BY 
    fleet.company_name; 

Answer 2

可以实现通过连接获得一个行的所有列，和混叠当您使用相同的表三次：

SELECT 
     fleet.company_name, 
     telephone.phone_number AS Telephone, 
     toll_free.phone_number AS Toll_Free, 
     fax.phone_number AS Fax 
    FROM 
     fleetseek.fleet 
    JOIN 
     fleet_phone 
    ON 
     fleet.fleet_id = fleet_phone.fleet_id 
    LEFT OUTER JOIN (SELECT 
      phone_number 
      FROM 
      fleetseek.phone_number, 
      fleetseek.phone_type 
      WHERE 
      phone_number.phone_type_id = phone_type.phone_type_id AND 
      phone_type.name = 'Telephone') telephone 
    ON 
     fleet_phone.phone_number_id = telephone.phone_number_id 
    LEFT OUTER JOIN 
     (SELECT 
     phone_number 
     FROM 
     fleetseek.phone_number, 
     fleetseek.phone_type 
     WHERE 
     phone_number.phone_type_id = phone_type.phone_type_id AND 
     phone_type.name = 'Toll Free') toll_free 
    ON 
     fleet_phone.phone_number_id = toll_free.phone_number_id 
    LEFT OUTER JOIN 
     (SELECT 
     phone_number 
     FROM 
     fleetseek.phone_number, 
     fleetseek.phone_type 
     WHERE 
     phone_number.phone_type_id = phone_type.phone_type_id AND 
     phone_type.name = 'Fax') fax 
    ON 
     fleet_phone.phone_number_id = fax.phone_number_id; 

通用设计选择的建议：把你的如果该信息非常重要，则电话会在与您的电话号码相同的表格中键入。不得不添加连接会影响性能（同时影响空间）。通常，性能比空间更昂贵。