postgres查找靠近特定邮政编码的邮政编码 [英] postgres find zip codes near specific zip code

问题描述

假设一个Postgres表包含zip作为varchar(10)，我想获取匹配特定zip的所有结果，或者在没有足够结果的情况下使用接近查询的条目扩展我的结果.说:

Assuming a Postgres Table containing zip as varchar(10) I want to get either all results matching a specific zip or extend my results with entries close to the queried one in case there are not enough results. Say:

用户搜索zip"56500"，我的结果集返回2个运行完全匹配的项目.在这种情况下，我想执行一种like查询，以查找"565％"条目.最终，我需要在一个查询中运行它.

A user searches for zip "56500" and my result-set returns 2 items running an exact match. In this case I want to perform a kind of like query that finds "565%" entries. Eventually I need to run this in one query.

有什么建议吗?

推荐答案

您可能想要这样的东西:

Something like this might be what you want:

SELECT …
FROM atable
WHERE zip = @zip

UNION ALL

SELECT …
FROM atable
WHERE NOT EXISTS (
  SELECT *
  FROM atable
  WHERE zip = @zip
)
  AND zip LIKE CONCAT(LEFT(@zip, 3), '%')

这可能不是最有效的解决方案，但是至少它是单个查询，因此可能作为起点很好.

This may not be the most efficient solution, but at least it is a single query so might do well as a starting point.

这篇关于postgres查找靠近特定邮政编码的邮政编码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！