22,209
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[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
*/
给你改了一下,楼上的语句基础上。没注意要分开来拍
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
--> 测试数据:[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
*/