数据库"编号"字段如下:
zd-1
DR-01
DR-02
DR-04
DR-02-01
DR-02-02
DR-07-01
DR-07-02
YC-DZ-01
DR-03
YC-DZ-02
DR-07
zd-2
DR-05
YC-DZ-03
DR-06
DR-07-03
DR-02-03
要求正确排序如下: 意思是截取后面的数字排序,先按第一个数字排,然后再按第二个数字排.zd-1
zd-2
DR-01
DR-02
DR-02-01
DR-02-02
DR-02-03
DR-03
DR-04
DR-05
DR-06
DR-07
DR-07-01
DR-07-02
DR-07-03
YC-DZ-01
YC-DZ-02
YC-DZ-03
zd-1
DR-01
DR-02
DR-04
DR-02-01
DR-02-02
DR-07-01
DR-07-02
YC-DZ-01
DR-03
YC-DZ-02
DR-07
zd-2
DR-05
YC-DZ-03
DR-06
DR-07-03
DR-02-03
要求正确排序如下: 意思是截取后面的数字排序,先按第一个数字排,然后再按第二个数字排.zd-1
zd-2
DR-01
DR-02
DR-02-01
DR-02-02
DR-02-03
DR-03
DR-04
DR-05
DR-06
DR-07
DR-07-01
DR-07-02
DR-07-03
YC-DZ-01
YC-DZ-02
YC-DZ-03
--try
order replace(replace(right(col,len(col-3)),'-',''),0,1)
消息 102,级别 15,状态 1,第 1 行
'replace' 附近有语法错误。
SELECT * FROM 货品 order replace(replace(right(编号,len(编号)-3),'-',''),0,1)
消息 102,级别 15,状态 1,第 1 行
'replace' 附近有语法错误。
呵呵
order by replace(replace(right(编号,len(编号)-3),'-',''),0,1)
选择列表中的列 '货品.id' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
乱了zd-1
DR-01
DR-02
DR-02-01
DR-02-02
DR-02-03
DR-03
DR-04
DR-04-01
DR-05
DR-06
DR-07
DR-07-01
DR-07-02
DR-07-03
zd-2
YC-DZ-01
YC-DZ-02
YC-DZ-03
/**
--功能:分割字符串,取第@i个值
--Author:josy(百年树人)
--参数@s:字符串
--参数@i:取第几个值
--参数@sign:分隔符
**/
create function [dbo].[f_col](@s varchar(100),@i int,@sign varchar(10))
returns varchar(20)
as
begin
declare @t table(id int identity(1,1),col varchar(10));
declare @cnt int,@rel varchar(20)
set @s=@s+@sign
set @cnt=datalength(@s)-datalength(replace(@s,@sign,''))
while @cnt>0
begin
insert @t(col) select left(@s,charindex(@sign,@s)-1)
set @cnt=@cnt-1
set @s=stuff(@s,1,charindex(@sign,@s),'')
end
select @rel=col from @t where id=@i
return @rel
end
go---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(8))
insert [tb]
select 'zd-1' union all
select 'DR-01' union all
select 'DR-02' union all
select 'DR-04' union all
select 'DR-02-01' union all
select 'DR-02-02' union all
select 'DR-07-01' union all
select 'DR-07-02' union all
select 'YC-DZ-01' union all
select 'DR-03' union all
select 'YC-DZ-02' union all
select 'DR-07' union all
select 'zd-2' union all
select 'DR-05' union all
select 'YC-DZ-03' union all
select 'DR-06' union all
select 'DR-07-03' union all
select 'DR-02-03'
---查询---
select *
from tb
order by
len(dbo.f_col(col,2,'-')),
right('00'+dbo.f_col(col,2,'-'),2),
len(dbo.f_col(col,3,'-')),
right('00'+dbo.f_col(col,3,'-'),2)---结果---
col
--------
zd-1
zd-2
DR-01
DR-02
DR-02-01
DR-02-02
DR-02-03
DR-03
DR-04
DR-05
DR-06
DR-07
DR-07-01
DR-07-02
DR-07-03
YC-DZ-01
YC-DZ-02
YC-DZ-03(18 行受影响)