mysql 查询PHP:我想将某个特定的项目放在第一位，并且可以修改查询要显示的项目数量 [英] mysql query PHP: I want to a specific items to be first and can modify the query how many items to be displayed

问题描述

我有下表.

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

我想获取所有车主，但如果 Jan 有 5 辆或更多车，我可以修改查询并让 Jan 的前四项出现在列表中.我不在乎我收到其余物品的顺序.我的首要任务是 Jan 应该是第一个.

I want to get all the car owners, but if Jan has 5 or more cars I can modify the query and get 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. My priority is Jan should be the first.

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

推荐答案

我觉得你需要枚举值，而变量是最简单的方法.然后，额外的联接为您提供了按表中最常见名称排序所需的信息:

I think you need to enumerate the values, and variables are the simplest way. Then, an additional join gives you the information you need to order by the most frequent names in the table:

select t.*
from (select t.*,
             (@rn := if(@n = name, @rn + 1,
                        if(@rn := name, 1, 1)
                       )
             ) as rn
      from t cross join
           (select @n := '', @rn := 0
      order by name
     ) t join
     (select name, count(*) as cnt
      from t
      group by name
     ) tn
     on t.name = tn.name
where rn <= 4
order by cnt desc, name;

这篇关于mysql 查询PHP:我想将某个特定的项目放在第一位，并且可以修改查询要显示的项目数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！