--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([text] varchar(6),[total] int)
insert [test]
select '1楼',8 union all
select '2楼',4 union all
select '五楼',0 union all
select '11楼',0 union all
select '四楼',0 union all
select '十二楼',0 union all
select '3楼',5
select * from test
order by len([text]),LEFT([text],1)
/*
text    total
1楼    8
2楼    4
3楼    5
四楼    0
五楼    0
11楼    0
十二楼    0
*/

------解决方案--------------------
SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'test')
BEGIN
    DROP TABLE [test]
END
GO
create table [test]([text] varchar(6),[total] int)
insert [test]
select '1楼',8 union all
select '2楼',4 union all
select '五楼',0 union all
select '11楼',0 union all
select '四楼',0 union all
select '十二楼',0 union all
select '3楼',5

select [text],total from test order by CASE CAST(LEFT([text],PATindex('%[^1234567890]%',[text]) - 1) AS INT) WHEN 0 THEN 100000
                                                                                                             ELSE CAST(LEFT([text],PATindex('%[^1234567890]%',[text]) - 1) AS INT) END,[text] ASC

text    total
1楼    8
2楼    4
3楼    5
11楼    0
十二楼    0
四楼    0
五楼    0

------解决方案--------------------
SQL code

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([text] varchar(6),[total] int)
insert [test]
select '1楼',8 union all
select '2楼',4 union all
select '五楼',0 union all
select '11楼',0 union all
select '四楼',0 union all
select '十二楼',0 union all
select '3楼',5


select [text],total from test 
order by CASE CAST(LEFT([text],PATindex('%[^1234567890]%',[text]) - 1) AS INT) 
WHEN 0 THEN 100000 END,LEN([text])
/*
text    total
1楼    8
2楼    4
3楼    5
11楼    0
四楼    0
五楼    0
十二楼    0
*/
         


给你改了一下，楼上的语句基础上。没注意要分开来拍

------解决方案--------------------
探讨

这个放一起写也可以，就是到二十几楼的时候，排序就不对了

引用:

SQL code

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([text] varchar(6),[total] int)
insert [test]
sel……