以条件为条件的顺序 [英] Order by conditioned to a field

问题描述

我想更改字段功能中的order by子句，但我不知道是否可能.

I would like to change the order by clause in function of a field, but I don't know whether it's possible or not.

这里是一个例子:

**TABLE DISCOUNTS**
category    offer
A           0
B           0
C           1
D           1
F           0

product_id  category    price
       1      A         100
       2      B         300
       3      C         250
       4      C         150
       5      D         200
       6      B         200
       7      F         250

我愿意选择所有已经订购的产品，首先，如果它们不是一个要约(我想先显示要约)，然后我想按类别对它们进行分组并按价格进行排序.

I'm willing to select all the products but already ordered, firstly if they are an offer or not (I want to show offers first), and after that I want to group them by category and sorted by price.

问题在于报价，这里按类别分组并不重要，因为报价不会太多，直接显示按价格排序更有意义.

The problem is with the offers, here is not important to group by category because won't be too many offers and makes more sense to directly show them sorted by price.

这是我得到的输出:

product_id  category    price       offer
4            C          150         1
3            C          250         1
5            D          200         1
1            A          100         0
6            B          200         0
7            F          250         0
2            B          300         0

虽然我想获得其他订单(仅按价格对报价进行排序):

While I would like to get a different order (sorting offers only by price):

product_id  category    price       offer
4            C          150         1
5            D          200         1
3            C          250         1
1            A          100         0
6            B          200         0
2            B          300         0
7            F          250         0

我正在使用的查询是:

select * from Products p
left join Discounts d on p.category = d.category
order by offer desc, p.category, price 

这是小提琴

这里最好的选择是什么? 谢谢.

What would be the best option here? Thanks.

推荐答案

据我了解，您希望商品仅按价格排序，而非商品按价格排序.在这种情况下，您可以使用IF有条件地排序:

As I understand it you want offers ordered by price only and non offers order by category then price. In that case you can use an IF to conditionally order:

select * from Products p
left join Discounts d on p.category = d.category
order by offer desc,IF(offer,price,p.category), p.category, price

这篇关于以条件为条件的顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！