Google Query中的确切结果，如果确切结果不存在，则进行部分匹配 [英] Exact result in Google Query, followed by partial match if exact result does not exist

问题描述

我使用Google Query搜索Google Spreadsheet： https://jsfiddle.net/ BaronGrivet / ctkpvuac / 3 /

示例数据：

  Block Hill Road 
 Greenhill Road 
 Hill Road 
 Brownhill Road 
  

示例查询：

 'SELECT A WHERE LOWER（A）LIKE％'+ searchInput +'％ORDER BY ASC '
  

如果有人搜索Hill，它将返回所有行。

然而，如果有人搜索Hill Road，它仍然会返回上面列出的Hill Road的所有行。

理想情况下，我希望它只能返回完全匹配的Hill Road，或者至少有Hill Road作为第一个响应。

是有一种方法来构建查询，以便精确匹配只显示或首先显示？

或者我需要编写一个后备脚本，我首先查询完全匹配，如果没有返回，则查询部分匹配？

（A：A，select * where LOWER（A）LIKE'hill road％'ORDER BY A desc）; query（A：A，select * where LOWER（A）LIKE'％hill road％'order BY a desc）}

给出命令：

 山路
山路
绿山路
布朗希尔路
座山路
  

（完全匹配是重复的），或者可能在列A中的所有条目中预留一个空格并应用：

  = {query（A：A，select * where LOWER（A）LIKE'％hill road％'ORDER BY A desc）; query（A：A ，select * where LOWER（A）LIKE'％hill road％'ORDER BY A desc）} 
  

<

 山路
 Block Hill道路
山道
 Greenhill Road 
 Brownhill Road 
 Block Hill Road 
  

如果您不想复制'exact'和'similar'，然后换成UNIQUE：

（a）不加空格：

  = unique（{query（A：A，select * where LOWER（A）LIKE'％hill road％'ORDER BY A desc ）; query（A：A，select * where LOWER（A）LIKE'％hill road％'ORDER BY A desc）}）
  

输出：

  Hill Road 
 Greenhill Road 
 Brownhill道路
 Block Hill Road 
  

（b）加上空格：

  = unique（{query（A：A，select * where LOWER（A）LIKE'％hill road％'ORDER BY A desc）;查询（A：A，select * where LOWER（A）LIKE'％hill road％'ORDER BY A desc）} 
  

输出：

 山路
 Block Hill Road 
 Greenhill Road 
 Brownhill Road 
  

$ b $如果选择UNIQUE命令 asc 可能会更好。

I'm using Google Query to search a Google Spreadsheet: https://jsfiddle.net/BaronGrivet/ctkpvuac/3/

Example Data:

Block Hill Road
Greenhill Road
Hill Road
Brownhill Road

Example Query:

'SELECT A WHERE LOWER(A) LIKE "%'+searchInput+'%" ORDER BY A ASC'

If someone searches for "Hill" it will return all of the rows.

However if someone searches for "Hill Road" it will still return all of the rows with "Hill Road" listed last.

Ideally I want it to either only return the exact match "Hill Road", or at least have "Hill Road" as the first response.

Is there a way to structure the query so that exact matches are either only shown or shown first?

Or would I have to script a fallback where I first query for an exact match and if that's not returned I query a partial match?

解决方案

Perhaps a step in the right direction:

={query(A:A,"select * where LOWER(A) LIKE 'hill road%' ORDER BY A desc");query(A:A,"select * where LOWER(A) LIKE '%hill road%' ORDER BY A desc")} 

to give the order:

Hill Road
Hill Road
Greenhill Road
Brownhill Road
Block Hill Road

(the exact match is duplicated), or possibly prepend a space to all entries in ColumnA and apply:

={query(A:A,"select * where LOWER(A) LIKE '% hill road%' ORDER BY A desc");query(A:A,"select * where LOWER(A) LIKE '%hill road%' ORDER BY A desc")}

to give:

 Hill Road
 Block Hill Road
 Hill Road
 Greenhill Road
 Brownhill Road
 Block Hill Road

If you would rather not have the 'exact' and 'similar' duplicated then wrap in UNIQUE:

(a) without added spaces:

=unique({query(A:A,"select * where LOWER(A) LIKE '% hill road%' ORDER BY A desc");query(A:A,"select * where LOWER(A) LIKE '%hill road%' ORDER BY A desc")})

output:

Hill Road
Greenhill Road
Brownhill Road
Block Hill Road

(b) with added spaces:

=unique({query(A:A,"select * where LOWER(A) LIKE '% hill road%' ORDER BY A desc");query(A:A,"select * where LOWER(A) LIKE '%hill road%' ORDER BY A desc")})

output:

 Hill Road
 Block Hill Road
 Greenhill Road
 Brownhill Road

If choosing UNIQUE order asc may make better sense.

这篇关于Google Query中的确切结果，如果确切结果不存在，则进行部分匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！