说实话，我特不愿意碰到这样的麻烦，但是碰到总是需要解决的，小弟实在无能，没好的经验。希望各位大牛能分享下。
表格TableName 有以下数据
beyond
Billie.Holiday
Bryan Adams
Craig David
Declan Galbraith
Dido
DJ Networx Vol.46
DJ Rap
按模糊条件 D 查询数据，以上数据都是经过筛选的，需要实现的排序是所有D开头的放前面，接着是D放第二位，以此类推。
赢的的结果是
Declan Galbraith
Dido
DJ Networx Vol.46
DJ Rap
beyond
Craig David
Bryan Adams
Billie.Holiday
还望各位大牛不吝赐教
------解决方案--------------------
- SQL code
create table tb(col varchar(30))insert into tbselect 'beyond' union allselect 'Billie.Holiday' union allselect 'Bryan Adams' union allselect 'Craig David' union allselect 'Declan Galbraith' union allselect 'Dido' union allselect 'DJ Networx Vol.46' union allselect 'DJ Rap'goselect colfrom tbwhere col like '%d%'order by charindex('d',col)drop table tb/***************col------------------------------Declan GalbraithDidoDJ Networx Vol.46DJ RapbeyondCraig DavidBryan AdamsBillie.Holiday(8 行受影响)
------解决方案--------------------
- SQL code
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (col nvarchar(28))insert into [TB]select 'beyond' union allselect 'Billie.Holiday' union allselect 'Bryan Adams' union allselect 'Craig David'union allselect 'Declan Galbraith' union allselect 'Dido' union allselect 'DJ Networx Vol.46' union allselect 'DJ Rap'select * from [TB]select colFROM TBORDER BY REPLACE(col,'d','[') asc/*Declan GalbraithDidoDJ Networx Vol.46DJ RapbeyondBillie.HolidayBryan AdamsCraig David*/
------解决方案--------------------
- SQL code
select * from tablename order by case when substring(col,1,1) = 'D' then 1 else 2 end,case when substring(col,2,1) = 'D' then 1 else 2 end,case when substring(col,3,1) = 'D' then 1 else 2 end,...case when substring(col,n,1) = 'D' then 1 else 2 end
------解决方案--------------------
- SQL code
--> 测试数据：#tbif object_id('tempdb.dbo.#tb') is not null drop table #tbcreate table #tb([name] varchar(50))insert #tbselect 'Declan Galbraith' union allselect 'Dido' union allselect 'DJ NetworxVol.46' union allselect 'DJ Rap' union allselect 'beyond' union allselect 'Craig David' union allselect 'Bryan Adams' union allselect 'Billie.Holiday'select * from #tb order by charindex('d',name)select * from #tb order by patindex('d',name)/*name--------------------------------------------------Declan GalbraithDidoDJ NetworxVol.46DJ RapbeyondCraig DavidBryan AdamsBillie.Holiday(8 行受影响)*/
------解决方案--------------------
- SQL code
select * from #tb order by replace(name,'d',' ')