尝试这个:
with a as
(
select * from (values
('1.1.2'),('1.1.3'),('2.1.4 '), ('2.1.5'), ('2.2.1') ) as b(c)
)
select c, PARSENAME(c,1),PARSENAME(c,2), PARSENAME(c,3)
from a
order by
convert(int,PARSENAME(c,3)),
convert(int,PARSENAME(c,2)),
convert(int,PARSENAME(c,1))
灵感来自:http://www.sql-server-helper.com/tips/sort-ip-address.aspx
with a as
(
select * from (values
('1.1.2'),('1.1.3'),('2.1.4 '), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as
(
select c,
convert(int,PARSENAME(c,3)) * 100
+ convert(int,PARSENAME(c,2)) * 10
+ convert(int,PARSENAME(c,1)) * 1 as the_value
from a
)
select c from x where the_value = (select MAX(the_value) from x)
在软件开发中,经常会遇到有两位数字的小版本号,版本号与数字值没有任何关系,因此版本1.12大于1.5;为了弥补这一点,你必须pad数字充分:
-- Use this, the query above is not future-proof :-)
with a as
(
select * from (values
('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as
(
select c,
convert(int,PARSENAME(c,3)) * 100*100*100
+ convert(int,PARSENAME(c,2)) * 100*100
+ convert(int,PARSENAME(c,1)) * 100 as the_value
from a
)
select c, the_value from x
order by the_value
Output:
2.1.4 2010400
2.1.5 2010500
2.1.12 2011200
2.2.1 2020100
如果您不考虑这一点(如以下查询):
with a as
(
select * from (values
('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as
(
select c,
convert(int,PARSENAME(c,3)) * 100
+ convert(int,PARSENAME(c,2)) * 10
+ convert(int,PARSENAME(c,1)) * 1 as the_value
from a
)
select c, the_value from x
order by the_value;
-- KorsG's answer has a bug too
with a as
(
select * from (values
('2.1.4 '), ('2.1.12'), ('2.1.5'), ('2.2.1') ) as b(c)
),
x as
(
select c,
CAST(REPLACE(c, '.', '') AS int) as the_value
from a
)
select c, the_value from x
order by the_value
这两个查询将产生相同(不正确)的输出:
c the_value
2.1.4 214
2.1.5 215
2.2.1 221
2.1.12 222
2.2.1 和 2.1.12 的值重叠。当您仅删除点并直接将结果字符串转换为 int 时,也会发生这种情况。 2.1.12变成2112,2.2.1变成221。 2.2.1大于2.1.12,不小于