MySQL中的特定顺序子句

My MySQL database contains Multiple products stored in 4 categories. All informations are in the same table :

我的MySQL数据库包含4个类别的多个产品。所有信息都在同一个表格中:

id - name - description - price - category

I'am trying to list them in this order. I wonder do this only by MySQL request if possible.

我想按这个顺序把它们列出来。如果可能的话，我不知道只能通过MySQL请求。

Cat1 : product-name-with-lower-price1
Cat2 : product-name-with-lower-price1
Cat3 : product-name-with-lower-price1
Cat4 : product-name-with-lower-price1

Cat1 : product-name-with-lower-price2
Cat2 : product-name-with-lower-price2
Cat3 : product-name-with-lower-price2
Cat4 : product-name-with-lower-price2

Cat1 : product-name-with-lower-price3
Cat2 : product-name-with-lower-price3
Cat3 : product-name-with-lower-price3
Cat4 : product-name-with-lower-price3

etc...

等等……

I'm sorry to not give any source code I tested because every thing I tried don't work. I think to use the MySQL Order By clause but I don't know how.

我很抱歉没有给出我测试过的任何源代码，因为我尝试过的所有东西都不能工作。我想用MySQL Order By子句，但是我不知道怎么用。

Many thx

很多谢谢

EDIT :

编辑:

Products are listed by lower price. After extract lower price of each Cat, we Order by price ASC. Then extract next lower-price of each cat and order by price ASC and so on...

产品以更低的价格上市。在每只猫抽取较低的价格后，我们按ASC价格订购。然后提取每只猫的下一个低价格，按价格ASC等…

So it could look like this :

它可以是这样的:

Cars : Aygo 8000$
Moto : Suzuki 8200$
Bus : Renault 8700$
Truck : Peugeot 9000$

Cars : Toyota 9300$
Bus  :  Renault 9400$
Truck : DMG 9600$
Moto : Harley 14000$


Bus : Mercedes 12000$
Moto : BMW : 18000$
Cars : Mercedes 11000$
Truck : Renault 10000$

2 个解决方案

#1

This is intended to be an improvement on Raina's approach. There are two issues with the approach. The first is the order by in the subquery. There is no guarantee that an order by in a subquery will produce results in the same order (it usually works in practice in MySQL, but you should not rely on the behavior). The second is having multiple variable assignments in the select. MySQL does not guarantee the order of assignments.

这是对Raina方法的改进。这种方法存在两个问题。第一个是子查询中的order by。不能保证子查询中的order将以相同的顺序生成结果(它通常在MySQL中工作，但是不应该依赖这种行为)。第二个是在select中有多个变量赋值。MySQL不保证分配的顺序。

A safer way of write this is:

更安全的写法是:

SELECT id, price, category 
FROM (SELECT (@rownum := IF(@cat = t.category, @rownum + 1, 
                            IF(@cat := t.category, 1, 1) 
                           )
             )criteria,
             t.* 
      FROM Table1 t
      ORDER BY category, price
     ) tt
ORDER BY tt.criteria, tt.category;

#2

One possible approach:

一个可能的方法:

SELECT id, price, category 
  FROM (
      SELECT @rownum := 
        (CASE
          WHEN @cat = t.category THEN @rownum + 1
          ELSE (@cat := t.category) IS NOT NULL
          END) criteria,
        t.* 
      FROM Table1 t
      ORDER BY category, price
  ) tt
ORDER BY tt.criteria, 
  FIELD(tt.category, 'Cat1', 'Cat2', 'Cat3');

SQL Fiddle. FIELD function is used to set up a specific ordering of categories; if you're ok with them sorted in alphabetical order, you can replace that just with ORDER BY tt.criteria, tt.category.

SQL小提琴。字段函数用于设置类别的特定排序;如果你对按字母顺序排序没问题的话，你可以用tt代替。标准,tt.category。

#1