如何显示额外的列而不是聚合函数或不在 group by 语句中

Question

我正在使用 postgis 来计算每个“a”与其相应的“b”类型点之间的距离。

到目前为止，我已经设法用这个查询来做到这一点：

SELECT a.name, min(ST_Distance(a.the_geom, b.the_geom)) as distance
FROM a, b
GROUP BY a.name;

这是有效的，但是它只列出了 a 类型点的名称以及到它最近的 b 类型点的距离，它没有列出最近的 b 类型点的名称每个都是我想要的类型点。

我通常会通过连接两个表来做到这一点，但它们不相关并且不共享键，所以我不知道如何连接它们。

Answer 1

暴力方法（假设PostGIS支持WITH子句，并假设我的WITH子句语法正确 - 两个未经检查的假设）：

WITH (SELECT a.name AS a_name, b.name AS b_name, ST_Distance(a.the_geom, b.the_geom) as distance
        FROM a, b  -- Cartesian product (not efficient!)
     ) AS distances
SELECT d1.a_name, d1.b_name, d1.distance
  FROM distances AS d1
  JOIN (SELECT a_name, MIN(distance) AS distance
          FROM distances
         GROUP BY a_name) AS d2

这会生成名称列表（a_name和b_name) 加上使用笛卡尔积的相应距离（这是您的原始查询在 GROUP BY 启动之前执行的操作）。然后，它选择两个名称以及该表与通过 a_name 分组并在 a_name 上连接的同一表的连接距离以及最小距离。

显然，如果不支持WITH，您可以将WITH 子句写出两次，并希望您的优化器是好的。