PostgreSQL动态商品属性左关联查询结果扁平化单行输出方案咨询

可以根据你的技术栈偏好选择以下三种方案：

方案1：纯SQL动态生成查询 #

适用于需要直接从数据库拿到结构化结果的场景：

1. 先统计单个商品关联的最大属性数量，用来确定需要生成的属性列对数：

SELECT MAX(attr_count) FROM (
  SELECT COUNT(*) AS attr_count FROM product_attributs GROUP BY product_id
) t;

2. 根据最大属性数动态拼接查询语句，通过窗口函数给同商品的属性编号后聚合：
   示例（假设最大属性数为2）：

SELECT 
  pr.name,
  pr.identifier_type,
  pr.identifier,
  pr.active,
  MAX(CASE WHEN att.rn = 1 THEN att.name END) AS attr_1_name,
  MAX(CASE WHEN att.rn = 1 THEN att.value END) AS attr_1_value,
  MAX(CASE WHEN att.rn = 2 THEN att.name END) AS attr_2_name,
  MAX(CASE WHEN att.rn = 2 THEN att.value END) AS attr_2_value
FROM products pr
LEFT JOIN (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY id) AS rn
  FROM product_attributs
) att ON pr.id = att.product_id
GROUP BY pr.id, pr.name, pr.identifier_type, pr.identifier, pr.active;

如果需要完全自动适配属性数量，可以把上述逻辑封装成PostgreSQL存储过程，动态拼接SQL后执行返回结果即可。

方案2：应用层处理（推荐） #

适用于大部分导出场景，实现和维护成本更低：

• 用你已有的左关联SQL查询所有数据，结果按商品id排序
• 在应用代码中按商品id分组，把同一个商品的所有属性收集到一个数组里
• 统计所有商品的属性数最大值，确定CSV表头的列数，表头格式为：基础商品字段 + 属性1名、属性1值、属性2名、属性2值...
• 逐行生成CSV内容，每个商品的基础字段直接填充，属性按顺序对应填充，不足的补空值

方案3：属性合并为单列 #

如果业务接受属性不拆分列，可直接用聚合函数生成键值对字符串，实现成本最低：

SELECT 
  pr.name,
  pr.identifier_type,
  pr.identifier,
  pr.active,
  STRING_AGG(CONCAT(att.name, ':', att.value), '; ') AS attributes
FROM products pr
LEFT JOIN product_attributs att ON pr.id = att.product_id
GROUP BY pr.id, pr.name, pr.identifier_type, pr.identifier, pr.active;

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