select * from tb order by (case when num=0 then 1 else 0 end),tm desc
select * from tb order by (case when num=0 then 0 else 1 end num desc,tm desc)
order by case when num = 0 then 1 else 0 end,tm desc
--创建表 CREATE table tb_dec ( id int identity(1,1), num int, tm datetime ) insert INTO tb_dec VALUES(20,'2011-09-03') insert INTO tb_dec VALUES(0,'2011-09-03') insert INTO tb_dec VALUES(0,'2011-09-03') insert INTO tb_dec VALUES(20,'2011-09-04') insert INTO tb_dec VALUES(0,'2011-09-06') --查询数据 select * from tb_dec order by case when num = 0 then 1 else 0 end,tm desc --结果集 4 20 2011-09-04 00:00:00.000 1 20 2011-09-03 00:00:00.000 5 0 2011-09-06 00:00:00.000 2 0 2011-09-03 00:00:00.000 3 0 2011-09-03 00:00:00.000
select * from tb order by (case when num=0 then 0 else 1 end num desc,tm desc)
CREATE table tb_dec
(
id int identity(1,1),
num int,
tm datetime
)
insert INTO tb_dec VALUES(20,'2011-09-03')
insert INTO tb_dec VALUES(0,'2011-09-03')
insert INTO tb_dec VALUES(0,'2011-09-03')
insert INTO tb_dec VALUES(20,'2011-09-04')
insert INTO tb_dec VALUES(0,'2011-09-06')
--查询数据
select * from tb_dec
order by case when num = 0 then 1 else 0 end,tm desc
--结果集
4 20 2011-09-04 00:00:00.000
1 20 2011-09-03 00:00:00.000
5 0 2011-09-06 00:00:00.000
2 0 2011-09-03 00:00:00.000
3 0 2011-09-03 00:00:00.000