MSSQL排序问题

[wcbgyjs]

我用的是MSSQL2005，有一表table1字段BH，VARCHAR(5)类型，有以下一些值
BH
C1-01
C10-1
C10-2
C11-1
C1-02

再对表table1做查询并BH排序时
select bh from table1 order by bh
出现了下面结果
BH
C1-01
C10-1
C1-02
C10-2
C11-1

而不是我们想要的结果：
BH
C1-01
C1-02
C10-1
C10-2
C11-1

经过研究发现，SQL2005在排序时好像忽略字符-，请问这是什么问题，先谢谢了！

[zzxap]

是这样的

[pt1314917]

--> 测试数据: [table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1] (BH varchar(5))
insert into [table1]
select 'C1-01' union all
select 'C10-1' union all
select 'C10-2' union all
select 'C11-1' union all
select 'C1-02'

select * from [table1] order by cast(substring(bh,2,charindex('-',bh)-2) as int),
cast(substring(bh,charindex('-',bh)+1,len(bh)) as int)

[swwei2002]

select * from # 
order by 
ascii(substring(bh,1,1)), 
ascii(substring(bh,2,1)), 
ascii(substring(bh,3,1)), 
ascii(substring(bh,4,1)), 
ascii(substring(bh,5,1))

[ws_hgo]

http://blog.csdn.net/ws_hgo/archive/2009/02/06/3866925.aspx

--(1)借用字符串进行无规律排序   
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',')  

[ws_hgo]

/*
C1-01 
C1-02 
C10-1 
C10-2 
C11-1 
*/
declare @sql varchar(100)   
set @sql=N'C1-01,C1-02,C10-1,C10-2,C11-1'  
  
select * from table1
order by charindex(N','+BH+N',',N','+@sql+N',')  

[hui_hui_2007]

关注。

[htl258_Tony]

--借用2楼测试数据：
--> 测试数据: [table1]
if object_id('[table1]') is not null drop table [table1]
go
create table [table1] (BH varchar(20))
insert into [table1]
select 'C1-01' union all
select 'C10-1' union all
select 'C10-2' union all
select 'C11-1' union all
select 'C1-02'

select * from [table1] order by parsename(stuff(replace(bh,'-','.'),1,1,''),2)*1,parsename(replace(bh,'-','.'),1)*1

BH
--------------------
C1-01
C1-02
C10-1
C10-2
C11-1

(5 行受影响)

[kye_jufei]

use substring

select * from [table1] order by cast(substring(bh,2,charindex('-',bh)-2) as int),
cast(substring(bh,charindex('-',bh)+1,len(bh)) as int)

[fcuandy]

如果值只长5,值中只有一个"-"符号，那么2,8楼的朋友就可以了。

如果值不确定长度，"-"的个数也不确实，那么写法稍复杂些， 但是思路很简单，就是补位

比如每一段长为2
C1-01
C10-1
C10-2
C11-1
C1-02
则补为

C01-01
C10-01
C10-02
C11-01
C01-02

然后用派生的列去排序即可。

[dawugui]

create table tb (BH varchar(5))
insert into tb
select 'C1-01' union all
select 'C10-1' union all
select 'C10-2' union all
select 'C11-1' union all
select 'C1-02'
go

select * from tb order by cast(replace(left(bh , charindex('-',bh)-1),'C','') as int) , cast(substring(bh , charindex('-',bh)+1 , len(bh)) as int)  

drop table tb

/*
BH    
----- 
C1-01
C1-02
C10-1
C10-2
C11-1

（所影响的行数为 5 行）

*/

[wcbgyjs]

经研究CHINESE_PRC_AS默认排序规则确实忽略字符-，要解决此问题只需将排序规则更改为CHINESE_PRC_BIN即可
谢谢各位，结帐！！！