在mysql语句中按ORDER BY CASE

I have been checking out the forum a lot for this question but as I am just a beginner I could not find an answer that I could use to my code work with my little knowledge. This is what I am trying to solve:

我一直在为这个问题查看论坛,但由于我只是一个初学者,我无法找到一个答案,我可以用我的知识对我的代码工作。这就是我想要解决的问题:

I have many products in my shop. There are 4 different types.

我店里有很多产品。有4种不同的类型。

1. product_name starts with "AA-"

product_name以“AA-”开头

2. product_name starts with "BB-"

product_name以“BB-”开头

3. product_name starts with "CC-"

product_name以“CC-”开头

4. product_name starts with "DF-"

product_name以“DF-”开头

No when I expose the products to the viewer I want to use ORDER BY ASC.

当我将产品暴露给观众时,我想使用ORDER BY ASC。

But with product "CC-" en "DF-" I want it to be ordered DESC.

但是对于产品“CC-”en“DF-”,我希望它能被订购DESC。

And if my product_name starts with "BB-" it should be ordered by price ( which is also called in the select statement )

如果我的product_name以“BB-”开头,则应按价格排序(也在select语句中调用)

I learned I should use CASE for this. But I don't know how exactly.

我知道我应该使用CASE。但我不知道究竟是怎么回事。

1 个解决方案

#1

I think you can also go with UNION in this case easily, like this :

我认为在这种情况下你也可以轻松地使用UNION,如下所示:

select t1.product_name Name, t1.product_price Price from

 (select product_name, product_price from shop 
  where product_name like 'cc%' or product_name like 'de%'
  order by product_name desc) t1

 union 

 select t2.* from
 ( select  product_name, product_price from shop 
   where product_name like 'bb%'
   order by product_price ) t2

 union 

 select t3.* from
 ( select product_name, product_price from shop 
   where ( product_name not like 'cc%') and ( product_name not like 'de%')
   and (product_name not like 'bb%') 
   order by  product_name, product_price) t3 ;