联接表上的postgreSQL最小值

Question

考虑具有列name和geom的表nyc_streets和nyc_subway_stations。对于三条特定的街道，我想找到10个最近的具有ST_Distance功能的地铁站。我试着从这个开始，但失败了：

SELECT street.name, subway.name
FROM nyc_streets AS street INNER JOIN nyc_subway_stations AS subway 
HAVING ST_Distance(street.geom, subway.geom) == min(ST_Distance(street.geom, subway.geom))
AND street.name in ('Elder Ave', 'Castle Hill Ave', '4th Ave')
GROUP BY street.name, subway.name ORDER BY subway.name;

如何处理这个问题？

Answer 1

使用row_number()按距离排序，选择前10名。假设id是street的PK

SELECT street_name, subway_name
FROM (
SELECT street.name street_name, subway.name subway_name
, row_number() over(partition by street.id order by ST_Distance(street.geom, subway.geom)) rn
FROM nyc_streets AS street 
CROSS JOIN nyc_subway_stations AS subway 
WHERE street.name in ('Elder Ave', 'Castle Hill Ave', '4th Ave')
) t
WHERE rn <= 10