mysql查询PHP:我要先将特定项目，然后再对其余项目进行排序 [英] mysql query PHP: I want to a specific items to be first and then to sort the rest of the items

问题描述

我有下表.

id  | car_name | owner
-------------------------
1   | Toyota   | Jan
2   | Ford     | Mike
3   | Isuzu    | Andrew
4   | BMW      | Jan
5   | Ferrari  | Steve
6   | Audi     | Jan
7   | Benz     | Klark
8   | Hyundai  | Jan
9   | Kia      | Jan

我想吸引所有车主，但Jan希望有5辆车，我希望Jan的前四项在列表中.我不在乎我收到其余物品的顺序.这样.

I want to get all the car owners, but Jan has 5 cars I want the first four item of Jan to be in the list. I don't care about the order that I receive the rest of the items. Like this.

id  | car_name | owner
-------------------------
1   | Toyota   | Jan
4   | BMW      | Jan
7   | Benz     | Jan
8   | Hyundai  | Jan
2   | Ford     | Mike
3   | Isuzu    | Andrew
5   | Ferrari  | Steve
6   | Audi     | Bob
9   | Kia      | Jan

我该怎么做?谢谢

推荐答案

您可以通过以下任意一种方式订购

You can order by any one of the following

order by owner <> 'Jan'
order by owner = 'Jan' desc
order by case when owner = 'Jan' then 0 else 1 end
order by if(owner = 'Jan',0,1)

owner = 'Jan'为True生成1，为False生成0，因此为什么desc

owner = 'Jan' produces 1 for True and 0 for False, hence why desc

此外，由于您不关心除前四行以外的其余行的顺序，因此最好将Jan行的其余行保持连续.

Also, since you don't care about the order of the rest of the rows except first four, you might as well keep the rest of Jan rows in continuation.

尝试一下:

SELECT 
    id, car_name, owner
FROM
    ((SELECT 
        0 x, t.*
    FROM
        your_table t
    ORDER BY owner <> 'Jan' , id
    LIMIT 4) UNION ALL (SELECT 
        *
    FROM
        (SELECT 
        1 x, t.*
    FROM
        your_table t
    ORDER BY owner <> 'Jan' , id
    LIMIT 4 , 1000) t
    ORDER BY id)) t
ORDER BY x , id;

仅当Jan具有4行或更多行时有效.

Works only if Jan has 4 or more rows.

这篇关于mysql查询PHP:我要先将特定项目，然后再对其余项目进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！