MySQL如何将满足空间相交条件的字符串值拼接合并到同一列

问题原因 #

你原有查询仅返回单条相交区域的原因是：GROUP BY targetGeo分组后没有对geos2.geoName字段使用聚合函数，数据库默认只会返回分组后的第一条匹配记录，无法合并所有相交区域的名称。

实现方案 #

要实现多值拼接效果，使用对应数据库的字符串聚合函数即可，不同主流数据库的实现代码如下：

MySQL 版本

SELECT 
  geos1.geoName AS targetGeo,
  GROUP_CONCAT(DISTINCT geos2.geoName ORDER BY geos2.geoName SEPARATOR ', ') AS intersectingGeos
FROM geos geos1
JOIN geos geos2 ON ST_INTERSECTS(geos1.geoPoly, geos2.geoPoly)
WHERE geos1.geoName <> geos2.geoName
GROUP BY geos1.geoName;

PostgreSQL / SQL Server 版本

SELECT 
  geos1.geoName AS targetGeo,
  STRING_AGG(DISTINCT geos2.geoName, ', ' ORDER BY geos2.geoName) AS intersectingGeos
FROM geos geos1
JOIN geos geos2 ON ST_INTERSECTS(geos1.geoPoly, geos2.geoPoly)
WHERE geos1.geoName <> geos2.geoName
GROUP BY geos1.geoName;

Oracle 版本

SELECT 
  geos1.geoName AS targetGeo,
  LISTAGG(DISTINCT geos2.geoName, ', ') WITHIN GROUP (ORDER BY geos2.geoName) AS intersectingGeos
FROM geos geos1
JOIN geos geos2 ON ST_INTERSECTS(geos1.geoPoly, geos2.geoPoly)
WHERE geos1.geoName <> geos2.geoName
GROUP BY geos1.geoName;

补充说明 #

• 代码中的DISTINCT用于过滤重复的相交区域名称，不需要去重可直接删除
• 分隔符', '可以自定义调整，比如换成换行符、顿号等
• 如果需要保留没有任何相交区域的记录（比如样例中的Paris），把JOIN改为LEFT JOIN即可，对应相交区域列会返回NULL，可通过COALESCE函数转换为空字符串或者自定义占位符

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