Google Query中的确切结果，如果确切结果不存在，则进行部分匹配

本文介绍了Google Query中的确切结果，如果确切结果不存在，则进行部分匹配的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我使用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 RoadGreenhill RoadHill RoadBrownhill RoadExample 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 RoadHill RoadGreenhill RoadBrownhill RoadBlock 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 RoadIf 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 RoadGreenhill RoadBrownhill RoadBlock 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 RoadIf choosing UNIQUE order asc may make better sense. 这篇关于Google Query中的确切结果，如果确切结果不存在，则进行部分匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！