字符串在gw里面排序总是这样的情况!
1
108
109
10
2
44
老问题了,有人说这样搞,但是放在动态查询中还是有问题?请问大家怎么办?
set @sql=@sql+'order by
left(ZYDA_Records.records_no,patindex('+'%[0-9]%'+',ZYDA_Records.records_no)-1),
substring(ZYDA_Records.records_no,patindex('+'%[0-9]%'+',ZYDA_Records.records_no),len(ZYDA_Records.records_no)-patindex('+'%[0-9]%'+',ZYDA_Records.records_no)-patindex('+'%[0-9]%'+',reverse(ZYDA_Records.records_no))+2)
,right(ZYDA_Records.records_no,patindex('+'%[0-9]%'+',ZYDA_Records.records_no)-1+(len(ZYDA_Records.records_no)-patindex('+'%[0-9]%'+',ZYDA_Records.records_no)-patindex('+'%[0-9]%'+',reverse(ZYDA_Records.records_no))+2)) '
exec(@sql)
帮看看为啥有错也行,不知道这样管不管用或者还有其他办法没?
1
108
109
10
2
44
老问题了,有人说这样搞,但是放在动态查询中还是有问题?请问大家怎么办?
set @sql=@sql+'order by
left(ZYDA_Records.records_no,patindex('+'%[0-9]%'+',ZYDA_Records.records_no)-1),
substring(ZYDA_Records.records_no,patindex('+'%[0-9]%'+',ZYDA_Records.records_no),len(ZYDA_Records.records_no)-patindex('+'%[0-9]%'+',ZYDA_Records.records_no)-patindex('+'%[0-9]%'+',reverse(ZYDA_Records.records_no))+2)
,right(ZYDA_Records.records_no,patindex('+'%[0-9]%'+',ZYDA_Records.records_no)-1+(len(ZYDA_Records.records_no)-patindex('+'%[0-9]%'+',ZYDA_Records.records_no)-patindex('+'%[0-9]%'+',reverse(ZYDA_Records.records_no))+2)) '
exec(@sql)
帮看看为啥有错也行,不知道这样管不管用或者还有其他办法没?
-------------------------------------------> 测试时间:2009-07-16
--> 我的淘宝:http://shop36766744.taobao.com/--------------------------------------------------if object_id('[TB]') is not null drop table [TB]
create table [TB]([tid] varchar(10))
insert [TB]
select 1 union all
select 108 union all
select 109 union all
select 10 union all
select 2 union all
select 44
select * from [TB] order by cast(tid as int)
/*
tid
----------
1
2
10
44
108
109(所影响的行数为 6 行)*/drop table TB--是这样吗?
left(ZYDA_Records.records_no,patindex(''%[0-9]%'',ZYDA_Records.records_no)-1),
substring(ZYDA_Records.records_no,patindex(''%[0-9]%'',ZYDA_Records.records_no),len(ZYDA_Records.records_no)-patindex(''%[0-9]%'',ZYDA_Records.records_no)-patindex(''%[0-9]%'',reverse(ZYDA_Records.records_no))+2)
,right(ZYDA_Records.records_no,patindex(''%[0-9]%'',ZYDA_Records.records_no)-1+(len(ZYDA_Records.records_no)-patindex(''%[0-9]%'',ZYDA_Records.records_no)-patindex(''%[0-9]%'',reverse(ZYDA_Records.records_no))+2)) '
exec(@sql)
还有A-002 A-003这样的序号改列为nvarchar类型
go
insert test select 1
insert test select 108
insert test select 109
insert test select 10
insert test select 2
insert test select 44
--解决的办法是进行补位处理
select *from test order by right('00'+aa,3)
/*
aa
----------
1
2
10
44
108
109(所影响的行数为 6 行)
*/
tid
----------
1
2
10
44
108
109
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a varchar(10))
go
insert into tb
select
'A-001' union all select
'C-001' union all select
'B-001' union all select
'D-001' union all select
'E-001' union all select
'E-002' union all select
'E-003' union all select
'E-005' union all select
'C-002' union all select
'B-004'
go
SELECT * FROM TB ORDER BY LEFT(A,1),RIGHT(A,3)
/*------------
----------
A-001
B-001
B-004
C-001
C-002
D-001
E-001
E-002
E-003
E-005
-------*/
create table [TB]([tid] varchar(10))
insert [TB]
select 1 union all
select 108 union all
select 109 union all
select 10 union all
select 2 union all
select 44
select * from [TB] order by right('0000000000'+tid,10) -- 在指定的列前加‘0’
drop table tb/* 结果tid
----------
1
2
10
44
108
109(6 row(s) affected)
*/
E-001
E-002
E-003
E-004
E-005
E-006
E-007也有这样的数据1
2
10
44
108
109 我希望这两种数据排序都别乱
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a varchar(10))
go
insert into tb
select
'9'union all select
'100'union all select
'A-001' union all select
'C-001' union all select
'B-001' union all select
'D-001' union all select
'E-001' union all select
'E-002' union all select
'E-003' union all select
'E-005' union all select
'C-002' union all select
'B-004'
go
SELECT * FROM TB ORDER BY LEFT('00000'+A,1),RIGHT('00000'+A,3) --就是看你的字段有多少个位 就补几个0 这样无论有字母不 就都可以啦
/*------------
a
----------
A-001
C-001
B-001
D-001
E-001
E-002
C-002
E-003
B-004
E-005
9
100
这样是两个列的搞定了 结贴了 谢谢各位!