日期:2014-05-17 浏览次数:20690 次
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [NUM] varchar(100));
insert #temp
select '1-1' union all
select '1-2' union all
select '2-1' union all
select '2-2' union all
select '10-1' union all
select '10-2' union all
select '20-1' union all
select '20-2' union all
select '100-1' union all
select '100-2' union all
select '200-1' union all
select '200-2'
--SQL:
select * from #temp
ORDER BY CAST(LEFT(num, CHARINDEX('-',num)-1) AS INT), CAST(RIGHT(num, LEN(num)-CHARINDEX('-',num)) AS INT)
DECLARE @tb TABLE(id VARCHAR(20))
INSERT INTO @tb
SELECT
'1-1' UNION ALL SELECT
'1-2' UNION ALL SELECT
'2-1' UNION ALL SELECT
'2-2' UNION ALL SELECT
'10-1' UNION ALL SELECT
'10-2' UNION ALL SELECT
'20-1' UNION ALL SELECT
'20-2' UNION ALL SELECT
'100-1' UNION ALL SELECT
'100-2' UNION ALL SELECT
'200-1' UNION ALL SELECT
'200-2'
SELECT * FROM @tb ORDER BY LEFT(id,CHARINDEX('-',id))
/*结果
id
1-1
1-2
10-1
10-2
100-1
100-2
2-1
2-2
20-1
20-2
200-1
200-2
*/