我表中的ID是这样的列---表名:test
1-1-ache
1-2-ache
1-11-jjj
1-23-jjll1
1-3-ache
我想按前面的1-1来排序，排出的序列是
1-1-ache
1-2-ache
1-3-ache
1-11-jjj
1-23-jjll1
应该怎样排？
------解决方案--------------------
- SQL code
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2011-10-12 15:20:35-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([col] varchar(10))insert [tb]select '1-1-ache' union allselect '1-2-ache' union allselect '1-11-jjj' union allselect '1-23-jjll1' union allselect '1-3-ache'--------------开始查询--------------------------select * from [tb] order by PARSENAME(REPLACE(col,'-','.'),1),PARSENAME(REPLACE(col,'-','.'),2)----------------结果----------------------------/* col----------1-1-ache1-2-ache1-3-ache1-11-jjj1-23-jjll1(5 行受影响)*/
------解决方案--------------------
- SQL code
select * from tb order by convert(int,left(col,charindex('-',col)-1)),convert(int,substring(col,charindex('-',col)+1,charindex('-',col,charindex('-',col)+1)-charindex('-',col)-1))
------解决方案--------------------
- SQL code
create table [tb]([col] varchar(10))insert [tb]select '1-1-ache' union allselect '1-2-ache' union allselect '1-11-jjj' union allselect '1-23-jjll1' union allselect '1-3-ache'goselect * from tb order by convert(int,left(col,charindex('-',col)-1)),convert(int,substring(col,charindex('-',col)+1,charindex('-',col,charindex('-',col)+1)-charindex('-',col)-1))/*col----------1-1-ache1-2-ache1-3-ache1-11-jjj1-23-jjll1(5 行受影响)*/godrop table tb
------解决方案--------------------
- SQL code
select * from tborder by REPLACE(col,'#','')--//结果id col----------- ----------------------------------------------------------------2 1#11#53 1#11#4580#7 1#11#4580#4581#84 1#11#5175#54 1#11#5175#5238#8 1#11#5175#5238#5272#85 1#11#5175#5547#55 1#11#5175#5547#5565#9 1#11#5175#5547#5565#5566#10 1#11#5175#5547#5565#5572#56 1#11#5175#5547#5573#11 1#11#5175#5547#5573#5574#57 1#11#5175#5547#5600#12 1#11#5175#5547#5600#5601#13 1#11#5175#5547#5600#5607#3 1#17#87 1#17#5843#86 1#17#5843#5844#58 1#17#5843#5844#5868#14 1#17#5843#5844#5868#5869#59 1#17#5843#5914#15 1#17#5843#5914#5915#4 1#21#89 1#21#6871#62 1#21#6871#6925#18 1#21#6871#6925#6926#90 1#21#6930#64 1#21#6930#6931#63 1#21#6930#6931#6937#19 1#21#6930#6931#6937#6940#20 1#21#6930#6931#6973#91 1#21#6930#7065#65 1#21#6930#7065#7066#21 1#21#6930#7065#7066#7072#22 1#21#6930#7065#7066#7082#92 1#21#6930#7135#66 1#21#6930#7135#7136#23 1#21#6930#7135#7136#7137#93 1#21#7176#67 1#21#7176#7234#24 1#21#7176#7234#7237#69 1#21#7176#7320#68 1#21#7176#7320#7321#25 1#21#7176#7320#7321#7323#26 1#21#7176#7320#7328#70 1#21#7176#7320#7330#27 1#21#7176#7320#7330#7331#28 1#21#7176#7320#7345#72 1#21#7176#7346#71 1#21#7176#7346#7347#29 1#21#7176#7346#7347#7353#30 1#21#7176#7346#7366#31 1#21#7176#7346#7368#82 1#21#7370#94 1#21#7370#7371#73 1#21#7370#7371#7392#32 1#21#7370#7371#7392#7399#74 1#21#7370#7371#7403#33 1#21#7370#7371#7403#7404#34 1#21#7370#7371#7403#7405#75 1#21#7370#7412#35 1#21#7370#7412#7421#76 1#21#7370#7425#36 1#21#7370#7425#7429#78 1#21#7370#7430#77 1#21#7370#7430#7431#37 1#21#7370#7430#7431#7435#38 1#21#7370#7430#7431#7438#39 1#21#7370#7430#7431#7439#40 1#21#7370#7430#7456#41 1#21#7370#7430#7457#80 1#21#7370#7458#79 1#21#7370#7458#7470#42 1#21#7370#7458#7470#7472#43 1#21#7370#7458#7475#81 1#21#7370#7483#44 1#21#7370#7483#7485#45 1#21#7370#7487#97 1#3#1 1#4#99 1#40#5 1#44#95 1#45#83 1#45#8994#46 1#45#8994#8995#47 1#45#8994#8996#48 1#45#8994#8997#49 1#45#8994#8998#50 1#45#8994#8999#51 1#45#8994#9000#52 1#45#8994#9001#96 1#52#88 1#52#6428#60 1#52#6428#6433#16 1#52#6428#6433#6440#61 1#52#6428#6466#17 1#52#6428#6466#6483#6 1#59#98 1#70#100 2#60#101 2#64#(101 行受影响)