排序,按数字大小从小到大,如何让空的排最后
有个价格字段sid,含小数点后两位.
要求,把小的排前面，如果价格为空,则空的排最后或按id大小排序.
如果价格不为空，并且相同,也是按自动编号的id大小排序
[解决办法]
SELECT * FROM 表
ORDER BY
CASE WHEN 价格 IS NULL
THEN ISNULL(价格,99999999)END
,
CASE WHEN 优惠 IS NULL
THEN ISNULL(优惠,99999999)END
[解决办法]
这个比较复杂,给出个各种不同测试数据比较直观.
declare @t table(id int, price int, preferential int)
insert into @t select 1, null, 2
union all select 2, 1, 45
union all select 6, 3, 4
union all select 3, null, null
union all select 4, 22, 3
union all select 5, 25, null
union all select 7, 3, 4
select * from @t order by
case when preferential is null then isnull(preferential, 9999999)end asc,
case when price is null then isnull(price, 9999999)end asc,
case when not (preferential is null and price is null) then isnull(preferential, 9999999) end asc,
id desc
(7 行受影响)
id price preferential
----------- ----------- ------------
4 22 3
7 3 4
6 3 4
2 1 45
1 NULL 2
5 25 NULL
3 NULL NULL
(7 行受影响)
