create table t1(a int , b varchar(10))
insert t1
select 1,'K1'
union all
select 2,'4'
union all
select 3,'T8'
union all
select 2,'1'
union all
select 3,'2'
union all
select 2,'K4'
union all
select 3,'3'
union all
select 2,'T1'
union all
select 3,'T4'SELECT * FROM T1 ORDER BY B
insert t1
select 1,'K1'
union all
select 2,'4'
union all
select 3,'T8'
union all
select 2,'1'
union all
select 3,'2'
union all
select 2,'K4'
union all
select 3,'3'
union all
select 2,'T1'
union all
select 3,'T4'SELECT * FROM T1 ORDER BY B
insert into t1 values(1,'11')
然后再执行
SELECT * FROM T1 ORDER BY B 你看看结果是不是的正确的。结果会导致为:2 1
1 11
3 2
3 3
2 4
1 K1
2 K4
2 T1
3 T4
3 T8
create table t1(a int , b varchar(10))
insert t1
select 1,'K1'
union all
select 2,'4'
union all
select 3,'T8'
union all
select 2,'1'
union all
select 3,'2'
union all
select 2,'K4'
union all
select 3,'3'
union all
select 2,'T1'
union all
select 3,'T4'insert into t1 values(1,'11')--查询脚本
declare @MaxLength int
select @MaxLength = max(len(b)) from t1select * from t1 order by Replicate('0',@MaxLength-len(b))+b
可以得到楼主要求的效果。
2 1
3 2
3 3
2 4
1 11
1 K1
2 K4
2 T1
3 T4
3 T8
楼主看看是否符合你的要求。
insert t1
select 1,'K1'
union all
select 2,'234'
union all
select 3,'T8'
union all
select 2,'110'
union all
select 3,'2'
union all
select 2,'K4'
union all
select 3,'32'
union all
select 2,'T1'
union all
select 3,'T4'SELECT * FROM T1
ORDER BY case when ISNUMERIC(b) = 1 then cast(b as int) else cast(left(cast(ascii(upper(left(b,1))) as varchar(100))+right(b,len(b)-1)+'00000000',8) as int) end