MySQL 按两列排序

MySQL Order by Two Columns

提问by user1187

I have a Table Like Below

我有一张如下表

    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');

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

我需要获得特色产品，然后是具有优先级 p1、p2 和 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

Could u plz spot mistake in that

你能找出错误吗

回答by ganesh

Try this

尝试这个

Select * from Products ORDER BY Featured, Priority

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.

如果您在 mysql 枚举上使用 ORDER BY，它不会按字母顺序排序，但会按其在枚举中的位置排序。

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 

回答by Yogendra Singh

Why don't you simply use SQL as :

为什么不简单地将 SQL 用作：

 SELECT * 
 FROM Products
  ORDER BY Featured desc,
        Priority asc;

By doing this Yeswill appear before No. P1will appear before P2and P2before P3. I believe, that is what you want.

通过这样做，Yes将出现在No. P1会出现在 之前P2和P2之前P3。我相信，这就是你想要的。

If data type issue in ordering then,

如果数据类型在排序中出现问题，

 SELECT * 
 FROM Products
  ORDER BY CONCAT(Featured) desc,
        CONCAT(Priority) asc;

回答by swapnesh

Check this query--

检查此查询--

 SELECT * 
 FROM Products
  ORDER BY Featured asc,Priority,ProductName asc;

Fiddle

小提琴

Working code check fiddle

工作代码检查小提琴

回答by aftab md

SELECT * 
    FROM Products
where Featured IN ('Yes') and
      Priority IN ('p1', 'p2', 'p3')
Order by Featured asc,Priority,ProductName asc;

This should work

这应该工作