有一个表内的数据如下: ID Name
"105388";"王五"
"105388";"马六"
"105388";"张三"
"105388";"李四"
"105388";"陈七"
"105844";"李四"
"105844";"陈七"
"105844";"王五"
"105844";"马六"
"105844";"张三"现在想变成如下排序方式,按 字符串"张三","李四","王五","马六","陈七" 来排序 ID Name
"105388";"张三"
"105388";"李四"
"105388";"王五"
"105388";"马六"
"105388";"陈七"
"105844";"张三"
"105844";"李四"
"105844";"王五"
"105844";"马六"
"105844";"陈七"
这个排序的字符串是无规则的.不知道能不能做到?
"105388";"王五"
"105388";"马六"
"105388";"张三"
"105388";"李四"
"105388";"陈七"
"105844";"李四"
"105844";"陈七"
"105844";"王五"
"105844";"马六"
"105844";"张三"现在想变成如下排序方式,按 字符串"张三","李四","王五","马六","陈七" 来排序 ID Name
"105388";"张三"
"105388";"李四"
"105388";"王五"
"105388";"马六"
"105388";"陈七"
"105844";"张三"
"105844";"李四"
"105844";"王五"
"105844";"马六"
"105844";"陈七"
这个排序的字符串是无规则的.不知道能不能做到?
create table #DepartMent
(
Depart varchar(10)
)
insert into #DepartMent select '组长'
union all select '助理'
union all select '总经理'
union all select '员工'
union all select '副总经理'
union all select '主管'
declare @sql varchar(100)
set @sql=N'总经理,副总经理,主管,组长,助理,员工'
select * from #DepartMent
order by charindex(N','+Depart+N',',N','+@sql+N',') 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2009/02/06/3866925.aspx
(
ID varchar(20),
Name varchar(20)
)
insert into #EE select '105388','王五'
insert into #EE select '105388','马六'
insert into #EE select '105388','张三'
insert into #EE select '105388','李四'
insert into #EE select '105388','陈七'
insert into #EE select '105844','李四'
insert into #EE select '105844','陈七'
insert into #EE select '105844','王五'
insert into #EE select '105844','马六'
insert into #EE select '105844','张三'declare @sql varchar(100)
set @sql=N'张三,李四,王五,马六,陈七'
select * from #EE
order by ID asc,charindex(N','+Name+N',',N','+@sql+N',') ID Name
-------------------- --------------------
105388 张三
105388 李四
105388 王五
105388 马六
105388 陈七
105844 张三
105844 李四
105844 王五
105844 马六
105844 陈七(10 行受影响)
order by ID asc,
case when Name='张三' then 0
else
case when Name='李四' then 1
else
case when Name='王五' then 2
else
case when Name='马六' then 3
else
4
end
end
end
end
ID Name
-------------------- --------------------
105388 张三
105388 李四
105388 王五
105388 马六
105388 陈七
105844 张三
105844 李四
105844 王五
105844 马六
105844 陈七(10 行受影响)
(
id int,
na varchar(20)
)--插入4条数据。
insert into stut values(1,'aa')
insert into stut values(2,'bb')
insert into stut values(3,'df')
insert into stut values(4,'中国')
select * from stut--根据'df,aa,中国,bb'来进行排序
select * from stut order by charindex(left(na,2),'df,aa,中国,bb') desc
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/jack15850798154/archive/2010/02/23/5319718.aspx
select a.*
from tb a,
(
select '张三' as name,1 as px
union select '李四',2
union select '王五',3
union select '马六',4
union select '陈七',5
) b
where a.name=b.name
order by b.px
create table #t
(
ID varchar(20),
Name varchar(20)
)
insert into #t select '105388','王五' union all
select '105388','马六' union all
select '105388','张三' union all
select '105388','李四' union all
select '105388','陈七' union all
select '105844','李四' union all
select '105844','陈七' union all
select '105844','王五' union all
select '105844','马六' union all
select '105844','张三'--语句
select id,name from #t
order by id,case when name='张三' then 0 when name='李四' then 1 when name='王五' then 2 when name='马六' then 3 when name='陈七' then 4 else name end