数据库查询以使用地址进行搜索 [英] Database query to search using address

问题描述

我正在开发一种搜索功能，可以根据我的项目的地址(即按邮政编码(zip)或按名称或按城市)搜索经销商.将仅向用户提供一个html输入字段来输入搜索词(用户一次只能输入一个搜索词，即邮政编码或名称或城市(不允许组合)).进行查询的条件如下

I am developing a search functionality to search for dealers by their address(i.e. by postcode(zip) or by name or by city)for my project. user will be provided with only one html input field to enter the search term (users can enter only one search term at a time, i.e. either zip code or name or city (combinations are not allowed)). The conditions to develop a query are as below

1. 用户通过邮政编码搜索时，匹配的结果应按降序显示
2. 按名称搜索用户时，匹配结果应按字母顺序显示
3. 与城市名相同

有时候，名称中也可能包含邮政编码，在这种情况下，结果应以邮政编码简称.

Some times there is a possibility that name can also contains zip code in that case results should be shorted by zip code.

我尝试过

select city, postcode, name
  from dealers
  where name ='xyz' OR
        postcode ='xyz' OR
        city='XYZ'
  ORDER BY postcode desc

但是我想为每种情况都使用"ORDER BY".例如'xyz'匹配我要按升序对结果进行排序的名字.我该怎么办?

But I want 'ORDER BY' for every condition. for example 'xyz' matches to the first name I want to sort the result in ascending order. How can I do that?

推荐答案

如果您的所有条款都按升序排序，那么生活会更轻松.

Life would be easier if all your terms were sorted in ascending order.

我假设您的实际应用程序使用的是变量而不是硬编码的字符串.因此，解决方案应如下所示:

I assume that your actual application is using a variable rather than a hard-coded string. So the solution should look something like this:

  select city, postcode, name
  from dealers
  where name = p_search_term OR
        postcode = p_search_term OR
        city = p_search_term
  ORDER BY case when postcode = p_search_term then p_search_term else 1 end desc
           , case when name = p_search_term then p_search_term else city end asc

这篇关于数据库查询以使用地址进行搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！