字符串形式的版本号比较

[jxf_yx]

有2表
iever表(当前系统ie版本)
id ver
1 5.0.3700.1000

ver表（ie版本历史记录）
id name ver1 ver2
1 ie5 sp3 5.00.3502.1000 5.0.3700.1000
2 ie5 sp4 5.0.3700.1000 5.50.3825.1300

==================
根据当前版本判断版本名字？

[xiaoku]

感觉 如果 统一格式的话就好办多了...如:5.00.3700.1000

[jxf_yx]

怎么 把后面2个小数点去了，然后转成数字

[dawugui]

不知道你的ID管用不?如果管用,如下.不过不用, 不要a.id = b.id
select b.id,b.name from iever a,
(
select id,name,ver1 as ver from ver
union all
select id,name,ver2 as ver from ver
) b
where a.id = b.id and a.ver = b.ver

[bugchen888]

SELECT name
FROM ver
WHERE ver2='5.0.3700.1000'

[jxf_yx]

to:dawugui(潇洒老乌龟)
当前版本 很可能是 一个是在区间的.

说白了就是要实现：

ver<5.0.3700.2000 <ver2

[jxf_yx]

就是要实现：

ver1<5.0.3700.2000 <ver2

[jxf_yx]

如果是数字的判断比较容易，但是是字符串，所以 就不能使用 ver1<ver<ver2 或者between了

[dawugui]

就是要实现：

ver1<5.0.3700.2000 <ver2
直接写不行吗?

select a.*,b.* from ver a,iever b where a.id = b.id and a.ver >=b.ver1 and a.ver <= b.ver2

[xiaoku]

--drop table iever ,ver
create table iever
(
id int ,
ver varchar(20)
)
insert into iever select 1,'5.0.3700.1000'

create table ver
(
id int ,
name varchar(10),
ver1 varchar(20),
ver2 varchar(20)
)
insert into ver select 1 ,'ie5 sp3', '5.00.3502.1000', '5.0.3700.1000'
union all select 2 ,'ie5 sp4', '5.0.3700.1000', '5.50.3825.1300'

select i.ver,v.name ,v.ver1,v.ver2
from iever i ,ver v
where cast(left(i.ver,charindex('.',right(i.ver,len(i.ver)-charindex('.',i.ver)))+charindex('.',i.ver)-1) as decimal(9,4))
>=cast(left(v.ver1,charindex('.',right(v.ver1,len(v.ver1)-charindex('.',v.ver1)))+charindex('.',v.ver1)-1) as decimal(9,4))
and cast(reverse(left(REVERSE(i.ver),charindex('.',right(REVERSE(i.ver),len(REVERSE(i.ver))-charindex('.',REVERSE(i.ver))))+charindex('.',REVERSE(i.ver))-1)) as decimal(9,4))
>cast(reverse(left(REVERSE(v.ver1),charindex('.',right(REVERSE(v.ver1),len(REVERSE(v.ver1))-charindex('.',REVERSE(v.ver1))))+charindex('.',REVERSE(v.ver1))-1)) as decimal(9,4))
and cast(left(i.ver,charindex('.',right(i.ver,len(i.ver)-charindex('.',i.ver)))+charindex('.',i.ver)-1) as decimal(9,4))
<=cast(left(v.ver2,charindex('.',right(v.ver2,len(v.ver2)-charindex('.',v.ver2)))+charindex('.',v.ver2)-1) as decimal(9,4))
and cast(reverse(left(REVERSE(i.ver),charindex('.',right(REVERSE(i.ver),len(REVERSE(i.ver))-charindex('.',REVERSE(i.ver))))+charindex('.',REVERSE(i.ver))-1)) as decimal(9,4))
<=cast(reverse(left(REVERSE(v.ver2),charindex('.',right(REVERSE(v.ver2),len(REVERSE(v.ver2))-charindex('.',REVERSE(v.ver2))))+charindex('.',REVERSE(v.ver2))-1)) as decimal(9,4))

----------我使用最笨的方法做的...数据量大的话,哪我的肯定很慢的
ver name ver1 ver2
-------------------- ---------- -------------------- --------------------
5.0.3700.1000 ie5 sp3 5.00.3502.1000 5.0.3700.1000