34,838
社区成员




create table tb(col varchar(10))
insert into tb values('1.b')
insert into tb values('9.b')
insert into tb values('10.b')
insert into tb values('11.b')
insert into tb values('101.b')
insert into tb values('111.b')
insert into tb values('1001.b')
insert into tb values('1111.b')
go
select * from tb order by cast(replace(col,'.b','') as int)
select * from tb order by cast(left(col,charindex('.b',col)-1) as int)
drop table tb
/*
col
----------
1.b
9.b
10.b
11.b
101.b
111.b
1001.b
1111.b
(所影响的行数为 8 行)
*/
/*
col
----------
1
9
10
11
101
111
1001
1111
(所影响的行数为 8 行)
*/
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?
select * from tb order by cast(col as bigint) asc
create table tb(col varchar(10))
insert into tb values('1')
insert into tb values('9')
insert into tb values('10')
insert into tb values('11')
insert into tb values('101')
insert into tb values('111')
insert into tb values('1001')
insert into tb values('1111')
go
select * from tb order by cast(col as int)
drop table tb
/*
col
----------
1
9
10
11
101
111
1001
1111
(所影响的行数为 8 行)
*/