select * from tb order by case [desc] when 'AAA' then 0 else 1 end,case [desc] when 'AAA' then name else Dept endtry
select ID , Name , Dept , Desc from 表 order by CHARINDEX('AAA', Name) desc, Dept
select * from tb order by case when charindex('AAA',[Desc])>0 then 0 else 1 end,[name],dept
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] int,[Name] varchar(1),[Dept] varchar(3),[Desc] varchar(7)) insert [tb] select 9,'f','001','aaabbc' union all select 8,'g','002','abcdefg' union all select 7,'c','003','xyaaahh' union all select 6,'a','004','kjdalkf'
---查询--- select * from [tb] order by case when charindex('AAA',[desc])>0 then 1 else 2 end, case when charindex('AAA',[desc])>0 then [name] else dept end ---结果--- ID Name Dept Desc ----------- ---- ---- ------- 7 c 003 xyaaahh 9 f 001 aaabbc 8 g 002 abcdefg 6 a 004 kjdalkf(所影响的行数为 4 行)
select ID , Name , Dept , Desc from 表 order by case when CHARINDEX('AAA', Name)>0 then 'A'+ Name else 'B'+Name end, Dept
select ID , Name , Dept from 表 order by case when CHARINDEX('AAA', Name)>0 then 'A'+ Name else 'B'+Name end, Dept
select ID , Name , Dept from 表 order by case when CHARINDEX('AAA', Name)>0 then 'A'+ Name else 'B' end, Dept这样应该OK了
--借用五楼的数据测了一下,作点小改: ---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([ID] int,[Name] varchar(1),[Dept] varchar(3),[Desc] varchar(7)) insert [tb] select 9,'f','001','aaabbc' union all select 8,'g','002','abcdefg' union all select 7,'c','003','xyaaahh' union all select 6,'a','004','kjdalkf' go select * from tb order by case when [desc] like '%AAA%' then 0 else 1 end,case when [desc] like '%AAA%' then name else Dept end /* ID Name Dept Desc ----------- ---- ---- ------- 7 c 003 xyaaahh 9 f 001 aaabbc 8 g 002 abcdefg 6 a 004 kjdalkf(4 行受影响) */
推荐用6楼的代码,另外用union也可以实现union实现两个记录集的合并,可处理十分复杂的需求,示例: select ID , Name , Dept from 表 where CHARINDEX('AAA', Name)>0 order by name union select ID , Name , Dept from 表 where CHARINDEX('AAA', Name)=0 order by dept
order by CHARINDEX('AAA', Name) desc, Dept
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Name] varchar(1),[Dept] varchar(3),[Desc] varchar(7))
insert [tb]
select 9,'f','001','aaabbc' union all
select 8,'g','002','abcdefg' union all
select 7,'c','003','xyaaahh' union all
select 6,'a','004','kjdalkf'
---查询---
select
*
from [tb]
order by
case when charindex('AAA',[desc])>0 then 1 else 2 end,
case when charindex('AAA',[desc])>0 then [name] else dept end ---结果---
ID Name Dept Desc
----------- ---- ---- -------
7 c 003 xyaaahh
9 f 001 aaabbc
8 g 002 abcdefg
6 a 004 kjdalkf(所影响的行数为 4 行)
order by case when CHARINDEX('AAA', Name)>0 then 'A'+ Name else 'B'+Name end, Dept
order by case when CHARINDEX('AAA', Name)>0 then 'A'+ Name else 'B'+Name end, Dept
order by case when CHARINDEX('AAA', Name)>0 then 'A'+ Name else 'B' end, Dept这样应该OK了
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Name] varchar(1),[Dept] varchar(3),[Desc] varchar(7))
insert [tb]
select 9,'f','001','aaabbc' union all
select 8,'g','002','abcdefg' union all
select 7,'c','003','xyaaahh' union all
select 6,'a','004','kjdalkf'
go
select * from tb order by case when [desc] like '%AAA%' then 0 else 1 end,case when [desc] like '%AAA%' then name else Dept end
/*
ID Name Dept Desc
----------- ---- ---- -------
7 c 003 xyaaahh
9 f 001 aaabbc
8 g 002 abcdefg
6 a 004 kjdalkf(4 行受影响)
*/
select ID , Name , Dept from 表 where CHARINDEX('AAA', Name)>0 order by name
union
select ID , Name , Dept from 表 where CHARINDEX('AAA', Name)=0 order by dept