PostgreSQL查询优化：将员工多手机号行合并为单行

原查询语句 #

with type as (
  select
                A.AREA_CODE || '-' || SUBSTR(A.DIAL_NUMBER,1,3) || '-' || SUBSTR(A.DIAL_NUMBER,4) ph,
                A.SOURCE_RECORD_OID,DESCRIPTION
        from
                tel A,
                tclient B
        where
                A.SOURCE_RECORD_TABLE = 'ASSO'
                and B.OID = A.poid and DESCRIPTION in ('Work Cell','Personal Cell','Work Phone','Home Phone'))
select distinct CA.userid,
        Cep.EMPLOYEEID,
        CA.GIVEN_NAME as firstName,
        CA.FAMILY_NAME as lastName,
        CA.MIDDLE_NAME as middleName,
        NVL(CA.BUSINESS_EMAIL_ADDRESS ,
        CA.PERSONAL_EMAIL_ADDRESS) EMAILADDRESS,
        case when type_client.DESCRIPTION = 'Work Cell' then type_client.ph end BUSINESSMOBILE,
        case when type_client.DESCRIPTION = 'Personal Cell' then type_client.ph end PERSONALMOBILE,
        case when type_client.DESCRIPTION = 'Work Phone' then type_client.ph end BUSINESSPHONE,
        case when type_client.DESCRIPTION = 'Home Phone' then type_client.ph end PERSONALPHONE
        from type type_client,
        GTT_TIMECARDEMP P,
        assoc CA,
        cepos cep
        where cep.employeeid = p.supervisorid
        and cep.userid = CA.userid
        and CA.OID = type_client.SOURCE_RECORD_OID;

当前输出 #

firstname  | lastname | middlename |         emailaddress          | businessmobile | personalmobile | businessphone | personalphone
------------+----------+------------+-------------------------------+----------------+----------------+---------------+---------------
 a          | b        |            |                               |                | yyy-xxx-0121   |               |
 a          | b        |            |                               | yyy-xxx-0121   |                |               |
 a          | b        |            |                               |                |                |               | yyy-xxx-0121
 b          | c        |            |                               |                | xxx-xxx-4869   |               |
 d          | e        |            |                               |                | xxx-xxx-2299   |               |
 f          | g        |            |                               |                | xxx-xxx-0496   |               |

预期输出 #

firstname  | lastname | middlename |         emailaddress          | businessmobile | personalmobile | businessphone | personalphone
--+------------+----------+------------+-------------------------------+----------------+----------------+---------------+---------------
  | a          | b        |            |                               | yyy-xxx-0121   | yyy-xxx-0121   |               | yyy-xxx-0121
  | b          | c        |            |                               |                | xxx-xxx-4869   |               |
  | d          | e        |            |                               |                | xxx-xxx-2299   |               |
  | f          | g        |            |                               |                | xxx-xxx-0496   |               |

修改后的查询语句 #

with type as (
  select
                A.AREA_CODE || '-' || SUBSTR(A.DIAL_NUMBER,1,3) || '-' || SUBSTR(A.DIAL_NUMBER,4) ph,
                A.SOURCE_RECORD_OID,
                DESCRIPTION
        from
                tel A,
                tclient B
        where
                A.SOURCE_RECORD_TABLE = 'ASSO'
                and B.OID = A.poid 
                and DESCRIPTION in ('Work Cell','Personal Cell','Work Phone','Home Phone'))
select 
        CA.userid,
        Cep.EMPLOYEEID,
        CA.GIVEN_NAME as firstName,
        CA.FAMILY_NAME as lastName,
        CA.MIDDLE_NAME as middleName,
        NVL(CA.BUSINESS_EMAIL_ADDRESS, CA.PERSONAL_EMAIL_ADDRESS) EMAILADDRESS,
        MAX(case when type_client.DESCRIPTION = 'Work Cell' then type_client.ph end) BUSINESSMOBILE,
        MAX(case when type_client.DESCRIPTION = 'Personal Cell' then type_client.ph end) PERSONALMOBILE,
        MAX(case when type_client.DESCRIPTION = 'Work Phone' then type_client.ph end) BUSINESSPHONE,
        MAX(case when type_client.DESCRIPTION = 'Home Phone' then type_client.ph end) PERSONALPHONE
from type type_client,
     GTT_TIMECARDEMP P,
     assoc CA,
     cepos cep
where cep.employeeid = p.supervisorid
  and cep.userid = CA.userid
  and CA.OID = type_client.SOURCE_RECORD_OID
GROUP BY CA.userid, Cep.EMPLOYEEID, CA.GIVEN_NAME, CA.FAMILY_NAME, CA.MIDDLE_NAME, 
         NVL(CA.BUSINESS_EMAIL_ADDRESS, CA.PERSONAL_EMAIL_ADDRESS);

修改说明 #

1. 移除原查询的DISTINCT，改用GROUP BY按员工唯一标识字段分组，确保同一员工的多条记录合并为单行。
2. 给每个CASE语句加上MAX()聚合函数：同一员工同一类型的号码只会有一条有效值，MAX()会提取该值，忽略其他行对应位置的NULL。
3. GROUP BY子句包含所有非聚合字段，保证分组逻辑的正确性。

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