order by (case when col1 = 'mmm' then 0 when col1 = 'kkk' then 1 when col1 = 'aaa' then 2 else 3 end)
order by charindex('abc,mmn,kkk',col)
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-03-07 11:57:02 -- Verstion: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[col] varchar(4)) insert [tb] select 1,'abc' union all select 2,'mmn' union all select 3,'kkk' union all select 4,'fgfg' union all select 5,'hhjm' --------------开始查询-------------------------- select * from [tb] order by case when charindex(col,'mmn,kkk,abc')>0 then 0 else 1 end ----------------结果---------------------------- /* id col ----------- ---- 1 abc 2 mmn 3 kkk 4 fgfg 5 hhjm(5 行受影响) */
select * from tb where col1 in ('abc', 'mmm', 'kkk') order by charindex(col1,'mmmkkkabc')
select * from tb where col1 in ('abc', 'mmm', 'kkk') order by col1 desc
use tempdb; GO /* create table tb ( col1 nvarchar(10) not null ); insert into tb(col1) values ('abc'),('mmm'),('kkk'); */ select * from tb where col1 in ('abc', 'mmm', 'kkk') order by ( case col1 when 'mmm' then 1 when 'kkk' then 2 when 'abc' then 3 end );
order by (case when col1 = 'mmm' then 0 when col1 = 'kkk' then 1 when col1 = 'aaa' then 2 else 3 end)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-07 11:57:02
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[col] varchar(4))
insert [tb]
select 1,'abc' union all
select 2,'mmn' union all
select 3,'kkk' union all
select 4,'fgfg' union all
select 5,'hhjm'
--------------开始查询--------------------------
select * from [tb] order by case when charindex(col,'mmn,kkk,abc')>0 then 0 else 1 end
----------------结果----------------------------
/* id col
----------- ----
1 abc
2 mmn
3 kkk
4 fgfg
5 hhjm(5 行受影响)
*/
order by charindex(col1,'mmmkkkabc')
use tempdb;
GO
/*
create table tb
(
col1 nvarchar(10) not null
);
insert into tb(col1)
values
('abc'),('mmm'),('kkk');
*/
select *
from tb
where col1 in ('abc', 'mmm', 'kkk')
order by
(
case col1
when 'mmm' then 1
when 'kkk' then 2
when 'abc' then 3
end
);