select * from tb order by case when receipt_time is null then 0 else 1 end,receipt_time desc
isnull(A,B) A:列名 B:替换值
默认null就是排最上---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-07-25 09:44:10 -- Version: -- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) -- Jun 10 2013 20:09:10 -- Copyright (c) Microsoft Corporation -- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([a] int,[b] int) insert [huang] select null,1 union all select null,2 union all select 3,4 --------------开始查询--------------------------select * from [huang] ----------------结果---------------------------- /* a b ----------- ----------- NULL 1 NULL 2 3 4 */
if object_id('[tb]') is not null drop table tb go create table tb([a] int,[b] int) insert tb select null,1 union all select null,2 union all select 3,4 union all select 2,4 union all select 5,4select * from tb order by isnull(a,1000000) desc
create table #A([time] datetime) insert into #A values ('2013-07-25') insert into #A values (NULL) insert into #A values ('2013-07-24') insert into #A values (NULL) insert into #A values ('2013-07-26')select * from #Aselect * from #A order by ISNULL([time],'9999-01-01') DESC
from tb
order by case when receipt_time is null then 0 else 1 end,receipt_time desc
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-07-25 09:44:10
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([a] int,[b] int)
insert [huang]
select null,1 union all
select null,2 union all
select 3,4
--------------开始查询--------------------------select * from [huang]
----------------结果----------------------------
/*
a b
----------- -----------
NULL 1
NULL 2
3 4
*/
if object_id('[tb]') is not null drop table tb
go
create table tb([a] int,[b] int)
insert tb
select null,1 union all
select null,2 union all
select 3,4 union all
select 2,4 union all
select 5,4select *
from tb
order by isnull(a,1000000) desc
create table #A([time] datetime)
insert into #A values ('2013-07-25')
insert into #A values (NULL)
insert into #A values ('2013-07-24')
insert into #A values (NULL)
insert into #A values ('2013-07-26')select * from #Aselect * from #A order by ISNULL([time],'9999-01-01') DESC