如何使用SQL提取指定类型ID并生成独立列展示

原表结构及数据 #

external_ids表的结构和数据如下：

需求说明 #

指定key_id列表（如'abcde','defghi'），将id_type为aps和product.id的identifier分别作为独立列，与key_id一同输出，期望结果：

错误的查询语句 #

当前使用的SQL无法实现需求：

select distinct
       identifier, identifier, key_id
from external_ids
where key_id in ('abcde','defghi')
  and id_type = (select identifier from external_ids
                 where id_type in ('aps','product.id')

正确的SQL实现方法 #

方法1：条件聚合（通用型，支持多数数据库） #

利用CASE WHEN结合聚合函数，按key_id分组提取对应类型的标识值：

SELECT
    MAX(CASE WHEN id_type = 'aps' THEN identifier END) AS aps,
    MAX(CASE WHEN id_type = 'product.id' THEN identifier END) AS `product.id`,
    key_id
FROM external_ids
WHERE key_id IN ('abcde', 'defghi')
  AND id_type IN ('aps', 'product.id') -- 过滤仅需的类型，提升效率
GROUP BY key_id;

说明：

• 每个key_id下aps和product.id各对应一条记录，用MAX()（或MIN()）可以确保提取到唯一的非空值；
• WHERE子句提前过滤目标key_id和id_type，减少分组时的数据处理量；
• 列名product.id包含点号，需用反引号（MySQL）或双引号（PostgreSQL/Oracle）包裹，避免语法错误。

方法2：自连接（逻辑直观，适合少量列转置） #

通过自连接关联同表中aps和product.id的记录：

SELECT
    aps.identifier AS aps,
    product.identifier AS `product.id`,
    aps.key_id
FROM external_ids aps
INNER JOIN external_ids product 
    ON aps.key_id = product.key_id
    AND product.id_type = 'product.id'
WHERE aps.id_type = 'aps'
  AND aps.key_id IN ('abcde', 'defghi');

说明：

• 以aps类型的记录为主表，关联同key_id下的product.id类型记录；
• 仅返回同时存在aps和product.id的key_id记录，若需保留缺失某类型的记录，可改用LEFT JOIN。

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