日期:2014-05-17 浏览次数:20860 次
--drop table t
create 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
*/
select row_number() over
(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 0
SELECT * FROM testorder ORDER BY a desc
/*
a
----------------------
1
0
-1
*/
SELECT *
FROM 表
ORDER BY Sign(Product_Price + 1) DESC,
Product_Price ASC