Mysql：按like排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3609166/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql: Order by like?
提问by dfens
assume that we are performing search using keywords: keyword1, keyword2, keyword3
假设我们正在使用关键字执行搜索：keyword1、keyword2、keyword3
there are records in database with column "name":
数据库中有列“名称”的记录：
1: John Doe 2: Samuel Doe 3: John Smith 4: Anna Smith
now Query:
现在查询：
SELECT * FROM users WHERE (name LIKE "%John%" OR name LIKE "%Doe%")
it will select records: 1,2,3 (in this order)
but i want to order it by keyword
in example keyword1=John, keyword2=Doeso it should be listed by keywords: 1,3,2 (because i want to perform search for "Doe" after searching for "John")
它将选择记录：1,2,3（按此顺序）但我想在示例中按关键字排序，keyword1=John, keyword2=Doe因此应按关键字列出：1,3,2（因为我想在之后执行搜索“Doe”搜索“约翰”）
I was thinking about SELECT DISTINCT FROM (...... UNION .....)but it will be much easier to order it somehow in another way (real query is really long)
我在想，SELECT DISTINCT FROM (...... UNION .....)但以另一种方式订购它会容易得多（真正的查询真的很长）
are there any tricks to create such order?
有什么技巧可以创建这样的订单吗？
回答by RedFilter
order by case
when name LIKE "%John%" then 1
when name LIKE "%Doe%" then 2
else 3
end
回答by fredley
Read up on Boolean Fulltext Searches, with which you can do ordering.
阅读Boolean Fulltext Searches，您可以使用它进行排序。
回答by Juan Tarquino
To build on RedFilter's answer, you could make the rows that have both keywords to be at the top:
要以 RedFilter 的答案为基础，您可以将具有两个关键字的行置于顶部：
order by case
when (name LIKE "%John%" and name LIKE "%Doe%") then 1
when name LIKE "%John%" then 2
when name LIKE "%Doe%" then 3
end
回答by Michael Pakhantsov
SELECT *
from
(
SELECT u.*, 1 OrderNum
FROM users
WHERE (name LIKE "%John%")
UNION
SELECT u.*, 2 OrderNum
FROM users
WHERE (name LIKE "%Doe%")
)
Order by OrderNum
回答by TarranJones
My example will Order all of the John's Alphabetically followed by the Doe's.
我的示例将按John字母顺序排列所有的's，然后是Doe's。
ORDER BY CASE
WHEN name LIKE "John%Doe" THEN CONCAT('a',name)
WHEN name LIKE "John%" THEN CONCAT('b',name)
WHEN name LIKE "%Doe" THEN CONCAT('c',name)
ELSE name
END
