select * from tb order by case when L3=2 then 0 else 1 end, L3
--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (ID int,L1 varchar(11),L2 varchar(11),L3 int) insert into #T select 1,'AA','BB',4 union all select 2,'CC','DD',2 union all select 3,'EE','FF',1 union all select 4,'GG','HH',3select * from #T order by case when L3=2 then 0 else 1 end, L3/* ID L1 L2 L3 ----------- ----------- ----------- ----------- 2 CC DD 2 3 EE FF 1 4 GG HH 3 1 AA BB 4 */
select * from #tab where L3=2 union select * from #tab where L3<>2 order by L3
--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (ID int,L1 varchar(11),L2 varchar(11),L3 int) insert into #T select 1,'AA','BB',4 union all select 2,'CC','DD',2 union all select 3,'EE','FF',1 union all select 4,'GG','HH',3select * from #T order by case when L3=2 then 0 else 1 end, L3/* ID L1 L2 L3 ----------- ----------- ----------- ----------- 2 CC DD 2 3 EE FF 1 4 GG HH 3 1 AA BB 4
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,L1 varchar(11),L2 varchar(11),L3 int)
insert into #T
select 1,'AA','BB',4 union all
select 2,'CC','DD',2 union all
select 3,'EE','FF',1 union all
select 4,'GG','HH',3select * from #T order by case when L3=2 then 0 else 1 end, L3/*
ID L1 L2 L3
----------- ----------- ----------- -----------
2 CC DD 2
3 EE FF 1
4 GG HH 3
1 AA BB 4
*/
select * from #tab where L3<>2 order by L3
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (ID int,L1 varchar(11),L2 varchar(11),L3 int)
insert into #T
select 1,'AA','BB',4 union all
select 2,'CC','DD',2 union all
select 3,'EE','FF',1 union all
select 4,'GG','HH',3select * from #T order by case when L3=2 then 0 else 1 end, L3/*
ID L1 L2 L3
----------- ----------- ----------- -----------
2 CC DD 2
3 EE FF 1
4 GG HH 3
1 AA BB 4