MySQL按两列排序 [英] MySQL Order by Two Columns

问题描述

我有一个像下面的表格

    CREATE TABLE Products(Product_id INT, ProductName VARCHAR(255), 
                          Featured enum('Yes', 'No'), Priority enum('p1', 'p2', 'p3'))


    INSERT INTO Products(ProductName, Featured, Priority) 
                  VALUES('Product A', 'Yes', 'p1'),
                        ('Product B', 'No',  'p2'),
                        ('Product C', 'Yes', 'p1'),
                        ('Product D', 'No',  'p1'),
                        ('Product E', 'Yes', 'p3'),
                        ('Product F', 'No',  'p2'),
                        ('Product G', 'Yes', 'p1'),
                        ('Product H', 'Yes', 'p2'),
                        ('Product I', 'No',  'p2'),
                        ('Product J', 'Yes', 'p3'),
                        ('Product K', 'Yes', 'p1'),
                        ('Product L', 'No',  'p3');

我需要获取特色产品，然后是优先级为p1，p2和p3的产品

I Need to get the Featured products followed by product with priority p1, p2 and p3

Op:
   ProdName | Featured  | Priority

    Product A   Yes         p1
    Product C   Yes         p1
    Product G   Yes         p1
    Product K   Yes         p1
    Product H   Yes         p2
    Product E   Yes         p3
    Product J   Yes         p3
    Product D   No          p1
    Product B   No          p2
    Product F   No          p2
    Product I   No          p2
    Product L   No          p3

我写了一个查询，在下面这个查询不起作用.

I Wrote a query below which ain't working..

                                           
  SELECT * 
    FROM Products
   ORDER BY Featured IN ('Yes') desc,
            Priority IN ('p1', 'p2', 'p3') desc

您能找出其中的错误吗

推荐答案

尝试一下

Select * from Products ORDER BY Featured, Priority

如果在mysql枚举上使用ORDER BY，则不会按字母顺序对其进行排序，而是按其在枚举中的位置对其进行排序.

If you use ORDER BY on mysql enum it will not order it by alphabetically but it will order it by its position in enum.

如果要按照描述的字母顺序进行排序，请枚举枚举名称 像这样的字符串

If you want to order alphabetically as you describe cast the enum name to a string like this

Select * from Products ORDER BY  concat(Featured) desc , Priority 

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