我有一字段类型是varchar(20),查询结果如下
ABC12-00001-06-1111
ABC12-00002-06-1111
ABC12-00003-06-1111
DCD12-00001-06-1111
DCD12-00002-06-1111
DCD12-00003-06-1111
ABC12-00141-06-1111
我希望得到如下结果
ABC12-00001-06-1111
ABC12-00002-06-1111
ABC12-00003-06-1111
ABC12-00141-06-1111
DCD12-00001-06-1111
DCD12-00002-06-1111
DCD12-00003-06-1111 将前一个编号相同的排序在一起就行了,
ABC12-00001-06-1111
ABC12-00002-06-1111
ABC12-00003-06-1111
DCD12-00001-06-1111
DCD12-00002-06-1111
DCD12-00003-06-1111
ABC12-00141-06-1111
我希望得到如下结果
ABC12-00001-06-1111
ABC12-00002-06-1111
ABC12-00003-06-1111
ABC12-00141-06-1111
DCD12-00001-06-1111
DCD12-00002-06-1111
DCD12-00003-06-1111 将前一个编号相同的排序在一起就行了,
insert test
select 'ABC12-00001-06-1111' union all
select 'ABC12-00002-06-1111' union all
select 'ABC12-00003-06-1111' union all
select 'DCD12-00001-06-1111' union all
select 'DCD12-00002-06-1111' union all
select 'DCD12-00003-06-1111' union all
select 'ABC12-00141-06-1111'select * from test order by coldrop table test
INSERT @T SELECT 'ABC12-00001-06-1111'
UNION ALL SELECT 'ABC12-00002-06-1111'
UNION ALL SELECT 'ABC12-00003-06-1111 '
UNION ALL SELECT 'DCD12-00001-06-1111'
UNION ALL SELECT 'DCD12-00002-06-1111'
UNION ALL SELECT 'DCD12-00003-06-1111'
UNION ALL SELECT 'ABC12-00141-06-1111'SELECT * FROM @T
ORDER BY
SUBSTRING(ID,1,5)
INSERT @t SELECT 'ABC12-00001-06-1111'
UNION ALL SELECT 'ABC12-00002-06-1111'
UNION ALL SELECT 'ABC12-00003-06-1111'
UNION ALL SELECT 'DCD12-00001-06-1111'
UNION ALL SELECT 'DCD12-00002-06-1111'
UNION ALL SELECT 'DCD12-00003-06-1111'
UNION ALL SELECT 'ABC12-00141-06-1111'SELECT * FROM @t ORDER BY n难道还有其它特殊要求?
( n varchar(20)) insert into #temp_id(n) values('ABC12-00001-06-1111')
insert into #temp_id(n) values('ABC12-00002-06-1111')
insert into #temp_id(n) values('ABC12-00003-06-1111')
insert into #temp_id(n) values('DCD12-00001-06-1111')
insert into #temp_id(n) values('DCD12-00002-06-1111')
insert into #temp_id(n) values('DCD12-00003-06-1111')
insert into #temp_id(n) values('ABC12-00141-06-1111')select * from #temp_id order by left(n,5),SUBSTRING ( n , 7 ,5 )
drop table #temp_id