如何将客户关联的手机号按phone1-phone9列输出实现无重复CSV导出

现有数据结构 #

• Customer表：存储客户基础信息，字段如下
  • ID：客户唯一标识
  • firstname：客户名
  • lastname：客户姓
    示例数据：ID=1对应John Doe，ID=2对应Max Payne
• Phones表：存储客户手机号，字段如下
  • ID：手机号记录唯一标识
  • CustomerId：关联Customer表的客户ID
  • number：手机号内容
    示例数据：ID1~ID3均关联CustomerId=1（对应3个手机号），ID4关联CustomerId=2（对应1个手机号）

需求说明 #

查询结果需要为每个客户生成1行无重复记录，手机号按顺序拆分为phone1到phone9共9列，单个客户最多保留9个手机号，无对应手机号的列留空，可直接导出为符合要求的CSV文件。

实现SQL语句 #

通用实现思路：先给每个客户的手机号按插入/关联顺序排序，再通过行转列聚合得到单条客户记录。

MySQL 8.0+/MariaDB 10.2+ 版本 #

WITH ranked_phones AS (
    SELECT 
        CustomerId,
        number,
        ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY ID) AS phone_rank
    FROM Phones
    HAVING phone_rank <=9
)
SELECT 
    c.ID AS customer_id,
    c.firstname,
    c.lastname,
    MAX(CASE WHEN rp.phone_rank = 1 THEN rp.number ELSE '' END) AS phone1,
    MAX(CASE WHEN rp.phone_rank = 2 THEN rp.number ELSE '' END) AS phone2,
    MAX(CASE WHEN rp.phone_rank = 3 THEN rp.number ELSE '' END) AS phone3,
    MAX(CASE WHEN rp.phone_rank = 4 THEN rp.number ELSE '' END) AS phone4,
    MAX(CASE WHEN rp.phone_rank = 5 THEN rp.number ELSE '' END) AS phone5,
    MAX(CASE WHEN rp.phone_rank = 6 THEN rp.number ELSE '' END) AS phone6,
    MAX(CASE WHEN rp.phone_rank = 7 THEN rp.number ELSE '' END) AS phone7,
    MAX(CASE WHEN rp.phone_rank = 8 THEN rp.number ELSE '' END) AS phone8,
    MAX(CASE WHEN rp.phone_rank = 9 THEN rp.number ELSE '' END) AS phone9
FROM Customer c
LEFT JOIN ranked_phones rp ON c.ID = rp.CustomerId
GROUP BY c.ID, c.firstname, c.lastname
ORDER BY c.ID;

低版本MySQL（无窗口函数支持） #

SELECT 
    c.ID AS customer_id,
    c.firstname,
    c.lastname,
    SUBSTRING_INDEX(GROUP_CONCAT(p.number ORDER BY p.ID SEPARATOR ','), ',', 1) AS phone1,
    IF(LENGTH(p.number_list) - LENGTH(REPLACE(p.number_list, ',', '')) >=1, SUBSTRING_INDEX(SUBSTRING_INDEX(p.number_list, ',', 2), ',', -1), '') AS phone2,
    IF(LENGTH(p.number_list) - LENGTH(REPLACE(p.number_list, ',', '')) >=2, SUBSTRING_INDEX(SUBSTRING_INDEX(p.number_list, ',', 3), ',', -1), '') AS phone3,
    IF(LENGTH(p.number_list) - LENGTH(REPLACE(p.number_list, ',', '')) >=3, SUBSTRING_INDEX(SUBSTRING_INDEX(p.number_list, ',', 4), ',', -1), '') AS phone4,
    IF(LENGTH(p.number_list) - LENGTH(REPLACE(p.number_list, ',', '')) >=4, SUBSTRING_INDEX(SUBSTRING_INDEX(p.number_list, ',', 5), ',', -1), '') AS phone5,
    IF(LENGTH(p.number_list) - LENGTH(REPLACE(p.number_list, ',', '')) >=5, SUBSTRING_INDEX(SUBSTRING_INDEX(p.number_list, ',', 6), ',', -1), '') AS phone6,
    IF(LENGTH(p.number_list) - LENGTH(REPLACE(p.number_list, ',', '')) >=6, SUBSTRING_INDEX(SUBSTRING_INDEX(p.number_list, ',', 7), ',', -1), '') AS phone7,
    IF(LENGTH(p.number_list) - LENGTH(REPLACE(p.number_list, ',', '')) >=7, SUBSTRING_INDEX(SUBSTRING_INDEX(p.number_list, ',', 8), ',', -1), '') AS phone8,
    IF(LENGTH(p.number_list) - LENGTH(REPLACE(p.number_list, ',', '')) >=8, SUBSTRING_INDEX(SUBSTRING_INDEX(p.number_list, ',', 9), ',', -1), '') AS phone9
FROM Customer c
LEFT JOIN (
    SELECT CustomerId, GROUP_CONCAT(number ORDER BY ID SEPARATOR ',') AS number_list FROM Phones GROUP BY CustomerId
) p ON c.ID = p.CustomerId
GROUP BY c.ID, c.firstname, c.lastname
ORDER BY c.ID;

提示：低版本MySQL使用GROUP_CONCAT时如果手机号过长或数量过多，需要提前调整group_concat_max_len参数，避免内容截断。

PostgreSQL/SQL Server版本 #

上述MySQL 8.0的CASE WHEN写法可直接复用，仅需调整窗口函数的语法兼容即可。

CSV导出操作 #

查询执行得到结果后，直接在数据库客户端选择导出为CSV格式即可，所有客户记录唯一无重复，空手机号列会自动留空符合要求。

内容的提问来源于stack exchange，提问作者Chris Kerngebiet