求一个关于排序的sql语句

[zhongzuo1981]

表结构
id bookCode
106 NULL
107 201001001
108 NULL
109 201001002
110 201001003
111 201002001
id是自增主键，bookCode到某一流程才会有值（9位的数字）
排序要求第一排序bookCode升序，第二排序id降序。

现在的结果是
108 NULL
106 NULL
107 201001001
109 201001002
110 201001003
111 201002001

希望的结果是
107 201001001
109 201001002
110 201001003
111 201002001
108 NULL
106 NULL

请问，语句能实现么？

[zhongzuo1981]

感觉效率上999999999的会高一点吧。
谢谢各位！！！

[zhongzuo1981]

一个比较麻烦的情况。
除了有值的行以为，还有一些行不是NULL而是‘’，
所以只能写成
order by (case when bookcode is null then 1 else 0 end),(case when bookcode='' then 1 else 0 end),bookcode,id desc
但再hibernate 里执行 case when 报错。
苦恼！

[叶子]

declare @table table (id int,bookCode varchar(12))
insert into @table
select 106,null union all
select 107,'201001001' union all
select 108,null union all
select 109,'201001002' union all
select 110,'201001003' union all
select 111,'201002001' union all
select 112,' '

select id, isnull(bookCode,' ') as bookCode
from @table 
order by isnull(replace(bookCode,' ',999999999),999999999),id desc
/*
id          bookCode
----------- ------------
107         201001001
109         201001002
110         201001003
111         201002001
112          
108          
106          
*/

[zhongzuo1981]

不是‘ ’ 是‘’，就是len=0的。这个方法就不行了。

[Quote=引用 12 楼 maco_wang 的回复:]
SQL code

declare @table table (id int,bookCode varchar(12))
insert into @table
select 106,null union all
select 107,'201001001' union all
select 108,null union all
select 109,'201001002' union……
[/Quote]

[zhongzuo1981]

replace 貌似识别不到''

[abuying]

select id, isnull(bookCode,' ') as bookCode
from @table 
order by replace(isnull(bookCode,''),'',999999999),id desc

[快溜]

select * from tb order by bookCode desc,id

[Xiao_Ai_Mei]

order by case when bookCode is null then 1 else 0 end,bookCode ,id desc 

[打一壶酱油]

  select * from tb order by bookCode ,id desc

[Linares]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int, bookCode int)
insert into #
select 106, null union all
select 107, 201001001 union all
select 108, null union all
select 109, 201001002 union all
select 110, 201001003 union all
select 111, 201002001

select * from # order by isnull(bookCode,'999999999'), id desc

/*
id          bookCode
----------- -----------
107         201001001
109         201001002
110         201001003
111         201002001
108         NULL
106         NULL
*/

[快溜]

create table tb (id int,bookcode varchar(10))
insert into tb

select 106,null union all
select 108,null union all
select 107,'201001001' union all
select 109,'201001002' union all
select 110,'201001003' union all
select 111,'201002001' 
drop table tb

select * from tb order by (case when bookcode is null then 1 else 0 end),id

/*
id          bookcode
----------- ----------
107         201001001
109         201001002
110         201001003
111         201002001
106         NULL
108         NULL

[feng2112]

[Quote=引用 2 楼 xiao_ai_mei 的回复:]

SQL code
order by case when bookCode is null then 1 else 0 end,bookCode ,id desc
[/Quote]
这个不错

[Linares]

#2,#5,#6:

select * from tb order by (case when bookcode is null then 1 else 0 end),bookcode,id

否则无法保证bookcode升序

[叶子]

declare @table table (id int,bookCode int)
insert into @table
select 106,null union all
select 107,201001001 union all
select 108,null union all
select 109,201001002 union all
select 110,201001003 union all
select 111,201002001

select * from @table order by isnull(bookCode,999999999),id desc
/*
id          bookCode
----------- -----------
107         201001001
109         201001002
110         201001003
111         201002001
108         NULL
106         NULL
*/

[叶子]

如果是9位的数字，最大的就是9个9 了，用isnull把null处理成9个9，个人认为即可满足你的排序要求。