sql - 在 Postgresql 中选择 SELECT 查询的最小结果

Question

我有以下查询，我想在其中计算距离“商店”类型建筑物最近的用户:

WITH y AS (
  SELECT t.userid as us, ST_Distance(t.thegeometry,b.thegeomtry) as dist
  FROM track t,buildings b
  WHERE b.type = 'store'
)
SELECT us 
FROM   y
WHERE  dist = (SELECT MIN(dist) FROM y)

问题是当我尝试计算最小值时它给了我 0，但这是不正确的。在我的数据中，ID 为 112 的用户与商店 A 之间的最小距离等于 2441 米。

Answer 1

您的查询应该如下所示:

SELECT t.userid AS us
      ,ST_Distance(t.thegeometry, b.thegeomtry) AS dist
FROM   track t
CROSS  JOIN buildings b
WHERE  b.type = 'store'
ORDER  BY dist 
LIMIT  1;

“找到离商店类型的任何建筑物最近的用户”。
不需要 CTE 或聚合函数。

您可能知道，对于更大的表，查询的开销非常大，因为您有效地在表 track 和 buildings 之间形成了有限的交叉连接 - 这意味着 O(N²).

我使用显式的 CROSS JOIN 语法(相当于逗号分隔的表列表)来说明这一点。