order by case when [order]=0 then 1 else 0 end,[order]
order by case when [order]=0 then 1 else 0 end,[order]海爷早》
order by case when [order]=0 then -[order] else [order] end
select * from tb order by case [order] when 0 then 1 else 0 end,[order]
---------------------------------------------------------------- -- Author :SQL77(只为思齐老) -- Date :2010-01-26 10:13:26 -- Version: -- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) -- Aug 6 2000 00:57:48 -- Copyright (c) 1988-2000 Microsoft Corporation -- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:#TB if object_id('tempdb.dbo.#TB') is not null drop table #TB go create table #TB([A] int) insert #TB select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 0 union all select 0 union all select 0 union all select 9 union all select 8 --------------开始查询--------------------------select * from #TB ORDER BY CASE WHEN A=0 THEN 1 ELSE 0 END ----------------结果---------------------------- /* (所影响的行数为 10 行)A ----------- 1 2 3 4 9 8 0 0 0 0(所影响的行数为 10 行) */
-- Author :SQL77(只为思齐老)
-- Date :2010-01-26 10:13:26
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([A] int)
insert #TB
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 0 union all
select 0 union all
select 0 union all
select 9 union all
select 8
--------------开始查询--------------------------select * from #TB ORDER BY CASE WHEN A=0 THEN 1 ELSE 0 END
----------------结果----------------------------
/* (所影响的行数为 10 行)A
-----------
1
2
3
4
9
8
0
0
0
0(所影响的行数为 10 行)
*/