求一个查询排序
小弟有一个表tb1里面用nchar存储名为cell1的列
1-1
1-2
2-1
2-2
10-1
10-2
20-1
20-2
100-1
100-2
200-1
200-2
通过select * from tb1 order by cell1得到的结果如下
100-1
100-2
10-1
10-2
1-1
1-2
200-1
200-2
20-1
20-2
2-1
2-2
我想请问各位大大，有办法得到上面那种排序结果吗？
[解决办法]
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
*/