order by 的时候，如何把null值排在最后面？？

[chuanyi]

表A：字段：ID
1，2，null，3，null，4，5…………

我想排序成：
1，2，3，4，5，null,null…………

最好不要使用union，因为union 不能使用两个order by，如果把order by 放在最后面，那就成了
null,null,null,1,2,3,4,5……
也不能使用select * from (select * from )这种复合的语句

[lsqkeke]

select * from tb order by (case when id is null then 999999 else id end )

999999那个数设置成比最大ID 还大的数字

[chuanyi]

不行，如果id超過999999999呢？id是不定的

[-狙击手-]

select * from tb order by (case when id is null then 1 else - id end ) desc

[-狙击手-]

错了，再想

[-狙击手-]

select * from @t order by (case when id is null then (select max(id)+1 from @t) else id end ) desc

[-狙击手-]

select * from @t order by (case when id is null then (select max(id)+1 from @t) else id end )

[lsqkeke]

to 楼上 ： 你的排序null在前面

[lsqkeke]

那就这样啊：
select * from @u order by (case when a is null then cast((select max(a) from @u where a is not null)as int)+1 else a end )

[lsqkeke]

简化一下：
select * from @u
order by (case when a is null then (select max(a) from @u)+1 else a end )

[terence4444]

select *
from A
ORDER BY (case WHEN id is NULL then 1 else 0 end)