我表中的ID是这样的列---表名:test1-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应该怎样排？
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应该怎样排？
-- 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 all
select '1-2-ache' union all
select '1-11-jjj' union all
select '1-23-jjll1' union all
select '1-3-ache'
--------------开始查询--------------------------select * from [tb] order by PARSENAME(REPLACE(col,'-','.'),1),PARSENAME(REPLACE(col,'-','.'),2)
----------------结果----------------------------
/* col
----------
1-1-ache
1-2-ache
1-3-ache
1-11-jjj
1-23-jjll1(5 行受影响)
*/
convert(int,substring(col,charindex('-',col)+1,charindex('-',col,charindex('-',col)+1)-charindex('-',col)-1))
insert [tb]
select '1-1-ache' union all
select '1-2-ache' union all
select '1-11-jjj' union all
select '1-23-jjll1' union all
select '1-3-ache'
go
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))
/*
col
----------
1-1-ache
1-2-ache
1-3-ache
1-11-jjj
1-23-jjll1(5 行受影响)*/
go
drop table tb
select * from tb
order 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 行受影响)
select * from tb
order by
parsename(REPLACE(col,'-','.'),1),
parsename(REPLACE(col,'-','.'),2)