我想从products表中获取数据。我必须按价格排序,但首先它显示的是那些价格在给定的最大值和最小值之间的人。
如果最小值=10,最大值=15
结果应该是这样的顺序: 产品1、产品4、产品5、产品3、产品2、产品6、产品7
谢谢
你也可以简单地将2个选项组合成一个 协会 .
select * from test where value >= 10 and value <= 15 union select * from test where value < 10 or value > 15
Select Demo
您可以使用CASE HWEN来确定这两个组。
价格中间有1英镑
SELECT * FROM product ORDER By CASE WHEN `price` BETWEEN 10 AND 15 Then 1 ELSE 2 END ASC, `price` ASC Title | price :----- | ----: Prod 1 | 10 Prod 4 | 11 Prod 5 | 12 Prod 3 | 15 Prod 2 | 8 Prod 6 | 20 Prod 7 | 24
SELECT * FROM product ORDER By CASE WHEN `price` BETWEEN 10 AND 15 Then 1 ELSE 2 END ASC, `price` ASC
Title | price :----- | ----: Prod 1 | 10 Prod 4 | 11 Prod 5 | 12 Prod 3 | 15 Prod 2 | 8 Prod 6 | 20 Prod 7 | 24
db<>不停摆弄 here
可以按布尔表达式排序 price BETWEEN 10 AND 15 DESC 先是 price :
price BETWEEN 10 AND 15 DESC
price
SELECT * FROM tablename ORDER BY price BETWEEN 10 AND 15 DESC, price;
看到了吗 demo .
