有2表
iever表(当前系统ie版本)
id ver
1 5.0.3700.1000ver表(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==================
根据当前版本判断版本名字?
iever表(当前系统ie版本)
id ver
1 5.0.3700.1000ver表(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==================
根据当前版本判断版本名字?
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
FROM ver
WHERE ver2='5.0.3700.1000'
当前版本 很可能是 一个是在区间的.说白了就是要实现:ver<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
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