模糊DISTINCT值 [英] fuzzy DISTINCT Values

问题描述

我有一个房地产列表数据库，需要返回社区列表.现在我正在使用mysql DISTINCT，它返回所有不同的值.我的想法是，有许多具有相似名称的社区:示例:

I have a database of real estate listings and need to return a list of neighborhoods. Right now I am using mysql DISTINCT which returns all of the distinct values. My probelm is that there is a lot of neighborhoods that have similar names: example:

Park View Sub 1
Park View
Park View Sub 2
Park View Sub 3
Great Lake Sub 1
Great Lake Sub 2
Great Lake 
Great Lake Sub 3

我正在寻找一个简单的php或mysql解决方案，该解决方案将识别"Park View"和"Great Lake"已经存在，并且仅返回"Park View"和"Great Lake".

I am looking for an easy php or mysql solution that would recognize that "Park View" and "Great Lake" already exists and ONLY return "Park View" and "Great Lake".

我最初的想法是如何按长度获取排序顺序，以使short值位于顶部，然后使用strstr循环遍历.这听起来像一个大任务，我想知道mysql或php中是否有一个函数可以轻松地做到这一点.

My initial thought is to some how get the sort order by length so that the short values are at the top and then loop through using strstr. This sound like a large task I am wondering if there is a function either in mysql or php that would easily do this.

推荐答案

您可以尝试以下方法；大概是在寻找完全匹配和接近匹配.

Here are some things you can try; presumably you're looking for both exact matches and close matches.

首先寻找完全匹配的内容.然后在REVERSED名称上查找LIKE匹配项.然后寻找多余字符最少的匹配项.

First look for an exact match. Then look for a LIKE match on the REVERSED name. Then look for the match with the fewest extra characters.

这是将执行所有操作的查询.请注意，如果您想提高效率，则需要将反向的地名存储在索引列中.

Here's a query that will do all that. Note that you will need to store the reversed place name in an indexed column if you want this to be efficient.

select name 
  from (
   select name, 0 ordinal
     from place 
    where name = 'Park View'
  union
  select name, 1 ordinal
    from place 
   where Reverse(Name) like concat(Reverse('Park View'),'%')
  union
  select name, 2+length(name)
    from place
   where name like concat('Park View','%')
 ) a 
order by ordinal
   limit 1

注意此UNION查询如何使用 ordinal 找出最佳匹配项.

Notice how this UNION query uses ordinal to figure out the best match.

在此处查看: http://sqlfiddle.com/#!2/76a97/9/0

这篇关于模糊DISTINCT值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！