mysql：按列名排序结果

$qry="select * from table where category='car' or title='car' or description='car'";

but I want the output to list the rows by category first and then title and then description.

但我希望输出首先按类别列出行，然后是标题，然后是描述。

****Edit: actually I am using a like operator to search**

****编辑：实际上我正在使用like运算符来搜索**

Example:

例：

 id category title description   
  1    car 
  2    car
  3            car
  4            car
  5            car
  6                    car

is there any way other than using union?
Thanks in advance.

除了使用联盟之外还有什么方法吗？提前致谢。

6 个解决方案

#1

You can do this using ORDER BY with the right keys. In MySQL, you can do:

您可以使用ORDER BY和正确的键来完成此操作。在MySQL中，您可以：

ORDER BY (category = 'car') DESC,
         (title = 'car') DESC,
         (description = 'car') DESC

MySQL treats boolean expressions as integers in a numeric context, with 0 for false and 1 for true. So the DESC puts the true versions first.

MySQL将布尔表达式视为数字上下文中的整数，0表示false，1表示true。所以DESC将真正的版本放在第一位。

You can also simplify the WHERE clause if you like:

如果您愿意，还可以简化WHERE子句：

WHERE 'car' IN (category, title, description)

#2

You can get this result by using this statement:

您可以使用以下语句获得此结果：

SELECT * FROM mytable 
WHERE (category='car' OR title='car' OR description='car')
ORDER BY category = 'car' DESC,
title = 'car' DESC,
description = 'car' DESC

How it works?

怎么运行的？

It will set the orders of data in DESC by sequentially as mentioned in query. You can change the sequence as you want.

如查询中所述，它将按顺序设置DESC中的数据顺序。您可以根据需要更改序列。

#3

Try this ,

SELECT * FROM table where 'car' IN (category, title, description)  ORDER BY category DESC, title DESC, description DESC

#4

You can use ORDER BY for multiple columns like:

您可以对多个列使用ORDER BY，例如：

SELECT * FROM tablename ORDER BY category DESC, title DESC, description DESC

I have tried it and it worked.

我已经尝试过它并且有效。

#5

You can have multiple ORDER BY clause in your query.

您的查询中可以有多个ORDER BY子句。

    select *from table where category='car' or title='car' or description='car' ORDER BY category DESC, title DESC, description DESC

See this answer for reference. mysql query order by multiple items

请参阅此答案以供参考。 mysql查询顺序由多个项目组成

#6

Try this Query :

试试这个查询：

select * from table 
where category='car' or title='car' or description='car' 
order by 1 desc, 2 desc, 3 desc

#1