试试这个:
--drop table tcreate table t(product_price numeric(10,1))insert into t
select -1.0 as product_price union all
select 0.0 union all
select 1.0 union all
select 2.0
go
select t.*
from t
inner join
(
select max(product_price) max_price from t
)tt
on 1=1
order by case when t.product_price = -1 then tt.max_price+1 else t.product_price end
/*
product_price
0.0
1.0
2.0
-1.0
*/
--drop table tcreate table t(product_price numeric(10,1))insert into t
select -1.0 as product_price union all
select 0.0 union all
select 1.0 union all
select 2.0
go
select t.*
from t
inner join
(
select max(product_price) max_price from t
)tt
on 1=1
order by case when t.product_price = -1 then tt.max_price+1 else t.product_price end
/*
product_price
0.0
1.0
2.0
-1.0
*/
(order by case when Product_Price<0 then 2 else 1 end,Product_Price)m,* from 表试试这个肯定可以的
--再简化下,
select * from 表 order by case when Product_Price<0 then 2 else 1 end,Product_Price
CREATE TABLE testorder
(a FLOAT)
INSERT INTO testorder
SELECT 1.0
UNION ALL
SELECT -1
UNION ALL
SELECT 0SELECT * FROM testorder ORDER BY a desc/*
a
----------------------
1
0
-1
*/
不过和排序规则也会有点关系
SELECT *
FROM 表
ORDER BY Sign(Product_Price + 1) DESC,
Product_Price ASC
union all
select * from testorder where a=-1