数据库表中房间门牌号字段如下:
1-甲-101
1-甲-1001
1-甲-102
1-乙-101
1-乙-301
1-乙-1603
1-丙-301
要求排序如下
1-甲-101
1-甲-102
1-甲-1001
1-乙-101
1-乙-301
1-乙-1603
1-丙-301
请高手指教!
1-甲-101
1-甲-1001
1-甲-102
1-乙-101
1-乙-301
1-乙-1603
1-丙-301
要求排序如下
1-甲-101
1-甲-102
1-甲-1001
1-乙-101
1-乙-301
1-乙-1603
1-丙-301
请高手指教!
drop table tb
Go
Create table tb([col] nvarchar(8))
Insert tb
select N'1-甲-101' union all
select N'1-甲-1001' union all
select N'1-甲-102' union all
select N'1-乙-101' union all
select N'1-乙-301' union all
select N'1-乙-1603' union all
select N'1-丙-301'
Go
Select *
from tb
order by
left([col],len(col)-charindex('-',reverse([col]))),
convert(int,right([col],charindex('-',reverse([col]))-1))
/*
col
--------
1-乙-101
1-乙-301
1-乙-1603
1-丙-301
1-甲-101
1-甲-102
1-甲-1001(7 row(s) affected)
*/
Insert tb
select N'1-甲-101' union all
select N'1-甲-1001' union all
select N'1-甲-102' union all
select N'1-乙-101' union all
select N'1-乙-301' union all
select N'1-乙-1603' union all
select N'1-丙-301'select * from tb
order by cast(parsename(replace(col,'-','.'),3) as int),
parsename(replace(col,'-','.'),2),
cast(parsename(replace(col,'-','.'),1) as int)/*
col
--------
1-丙-301
1-甲-101
1-甲-102
1-甲-1001
1-乙-101
1-乙-301
1-乙-1603(所影响的行数为 7 行)
*/drop table tb
Insert tb
select N'1-甲-101' union all
select N'1-甲-1001' union all
select N'1-甲-102' union all
select N'1-乙-101' union all
select N'1-乙-301' union all
select N'1-乙-1603' union all
select N'1-丙-301'select * from tb
order by cast(parsename(replace(col,'-','.'),3) as int),
case when parsename(replace(col,'-','.'),2) = '甲' then 1
when parsename(replace(col,'-','.'),2) = '乙' then 2
when parsename(replace(col,'-','.'),2) = '丙' then 3
end,
cast(parsename(replace(col,'-','.'),1) as int)/*
col
--------
1-甲-101
1-甲-102
1-甲-1001
1-乙-101
1-乙-301
1-乙-1603
1-丙-301(所影响的行数为 7 行)
*/drop table tb
对于中间一段甲乙丙的排序,如果按照拼音排则如5楼,想按照具体的顺序排,需要一一写出,如上.