用mysql排序多于一列 [英] Order by more than one column with mysql

问题描述

下面显示的是我的查询，我需要对这三列进行排序.这是用于复杂的搜索列表.如果我使用其中之一，它会完美地工作.但是，如果处理多个，则订购不正确.

The below shown is my query I need to order by with these three columns . This is for complex search listing. If I used one of them ,it works perfectly . But handling more than one ,it does not order properly.

编辑

实际上我需要，高级记录必须放在第一位，并需要更多的收藏夹，并且精英客户也要具有更高的优先级. 这些都是有条件的

Actually I needed ,premium records must be at first positions along with more favorites , and also elite customers have more priority . These all works conditionally

1)首先会有更多的收藏夹

1) More favorites will come at first

2)但是会考虑溢价

3)如果基本"有5个收藏夹"，则不会推送到顶部，基本"优先级低

3) If basic will have 5 favorites ,it wont push to top ,basic having small priority

4)精英也有一些优先权

4) As well elite have some good priority

SELECT  
   `Driver`.`id` ,  
   `Driver`.`login_id` ,  
   `Login`.`profile_type`, 
   .................
ORDER BY  `fav_count`, 
   FIELD(  `Login`.`profile_type` ,  "premium",  "basic" ) ,  
   `Driver`.`is_elite` ASC

我期望的采样结果

is_elite|       profile_type  | fav_count
________|_____________________|____________
1       |        premium      |   4      
1       |        premium      |   3 
1       |        premium      |   2 
1       |        premium      |   1 
0       |        basic        |   5 
0       |        basic        |   0

请告诉我.谢谢

推荐答案

如果想要列出的输出，则需要对ORDER BY中的值重新排序:

If you want the output you listed, you need to reorder the values in the ORDER BY:

ORDER BY `Driver`.`is_elite` DESC, FIELD(  `Login`.`profile_type` ,  "premium",  "basic" ) DESC, `fav_count` DESC

注意:您还需要在所有排序列上使用降序(DESC).他们每个人都被按从大到小的顺序排序.

Note: You also need to have descending (DESC) on all of your sort columns. Each of them is being sorted largest to smallest.

这篇关于用mysql排序多于一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！