I need a SQLite query that searches 1 field only using LIKE.
我需要一个仅使用LIKE搜索1字段的SQLite查询。
Basic example:
基本示例：
SELECT name FROM table WHERE name LIKE "%John%" ORDER BY name LIMIT 10;
The problem is that I want the result to be ordered in this way:
问题是我希望以这种方式排序结果：
- If the field is equal (e.g. "John")
- 如果该字段相等（例如“John”）
- If the field starts with "John" (e.g. "John Doe")
- 如果字段以“John”开头（例如“John Doe”）
- If the field contains "John" (e.g. "Jane John Doe")
- 如果该字段包含“John”（例如“Jane John Doe”）
The following query achieves the expected result, but is slow:
以下查询实现了预期结果，但速度很慢：
SELECT name FROM table WHERE name LIKE "%John%" ORDER BY CASE WHEN name = "John"
THEN 1 ELSE 2 END, CASE WHEN name LIKE "John%" THEN 1 ELSE 2 END, name LIMIT 10;
The query above is slower (or I tested it incorrectly) than the alternative of using 3 separate queries (one for exact match, one for starts with and one for contains).
上面的查询比使用3个单独的查询（一个用于完全匹配，一个用于开始，一个用于包含）的替代方法更慢（或者我测试不正确）。
Are there any other alternatives?
还有其他选择吗？
2 个解决方案
#1
15
Try in this way :
试试这种方式：
SELECT name
FROM table
WHERE name LIKE "%John%"
ORDER BY (CASE WHEN name = "John" THEN 1 WHEN name LIKE "John%" THEN 2 ELSE 3 END),name LIMIT 10 ;
#2
6
It should suffice to order on your equivalence tests:
在等效性测试中订购应该足够了：
ORDER BY name = "John" DESC, name LIKE "John%" DESC
ORDER BY clauses are evaluated from left to right.
ORDER BY子句从左到右进行评估。
#1
15
Try in this way :
试试这种方式：
SELECT name
FROM table
WHERE name LIKE "%John%"
ORDER BY (CASE WHEN name = "John" THEN 1 WHEN name LIKE "John%" THEN 2 ELSE 3 END),name LIMIT 10 ;
#2
6
It should suffice to order on your equivalence tests:
在等效性测试中订购应该足够了：
ORDER BY name = "John" DESC, name LIKE "John%" DESC
ORDER BY clauses are evaluated from left to right.
ORDER BY子句从左到右进行评估。