sql 汉字 数字混合排序,该怎么处理

sql 汉字 数字混合排序


text total

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

按 text 里面的数字排序，中文的排在后面，数字的排在前面，并按正序排

[解决办法]

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 allselect '2楼',4 union allselect '五楼',0 union allselect '11楼',0 union allselect '四楼',0 union allselect '十二楼',0 union allselect '3楼',5select * from testorder by len([text]),LEFT([text],1)/*text    total1楼    82楼    43楼    5四楼    0五楼    011楼    0十二楼    0*/
[解决办法]
SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'test')BEGIN    DROP TABLE [test]ENDGOcreate table [test]([text] varchar(6),[total] int)insert [test]select '1楼',8 union allselect '2楼',4 union allselect '五楼',0 union allselect '11楼',0 union allselect '四楼',0 union allselect '十二楼',0 union allselect '3楼',5select [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] ASCtext    total1楼    82楼    43楼    511楼    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 allselect '2楼',4 union allselect '五楼',0 union allselect '11楼',0 union allselect '四楼',0 union allselect '十二楼',0 union allselect '3楼',5select [text],total from test order by CASE CAST(LEFT([text],PATindex('%[^1234567890]%',[text]) - 1) AS INT) WHEN 0 THEN 100000 END,LEN([text])/*text    total1楼    82楼    43楼    511楼    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……