如何在PostgreSQL中创建含位置对应土壤层逗号拼接列的视图

在PostgreSQL中，你可以用**STRING_AGG**聚合函数实现和QGIS relation_aggregate 相同的拼接效果，并且创建的视图是动态的——每次查询都会自动同步原表的最新数据。

前提假设 #

假设你的表结构如下（可根据实际字段名调整）：

• locations 表：主键为 location_id（唯一标识每个位置），包含其他位置属性（如名称、坐标等）
• layers 表：外键 location_id 关联 locations 表，包含要拼接的字段 layer（即你QGIS中用到的expression:="layer"）

创建动态视图的SQL语句 #

CREATE OR REPLACE VIEW location_with_layers AS
SELECT
  l.location_id,
  l.location_name, -- 替换为你的locations表实际属性字段
  -- 拼接当前位置的所有土壤层，逗号分隔，可选排序保证顺序一致
  STRING_AGG(ly.layer, ', ' ORDER BY ly.layer) AS soil_layers
FROM
  locations l
LEFT JOIN layers ly ON l.location_id = ly.location_id
GROUP BY
  l.location_id, l.location_name; -- 必须包含locations表所有非聚合字段

关键说明 #

1. STRING_AGG 参数解析：
   • 第一个参数：要拼接的字段（这里是ly.layer）
   • 第二个参数：分隔符（和你QGIS里的concatenator:=', '一致）
   • 可选的ORDER BY：确保拼接的土层顺序固定，避免每次查询结果顺序混乱
2. 动态特性：这个视图不会存储静态数据，每次查询时都会实时关联locations和layers表计算最新的拼接结果，完全满足你生成动态符号体系的需求
3. 去重处理：如果layers表中同一个位置存在重复土层记录，可以在STRING_AGG中加DISTINCT：

   STRING_AGG(DISTINCT ly.layer, ', ' ORDER BY ly.layer) AS soil_layers
   

4. 左连接的作用：使用LEFT JOIN确保即使某个位置没有对应的土壤层，也会在视图中保留该行，soil_layers字段会显示为NULL（如果需要显示空字符串，可以用COALESCE(STRING_AGG(...), '')）

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