create table jl (id int ,name char(5),code char(5),revision varchar (10) )
insert into jl
select 12,'li','ABC','a.0'
union
select 13,'li','ABC','a.1.0'
union
select 14,'li','ABC','a'
union
select 15,'li','ABC','b.0.0'
union
select 16,'li','ABC','b'
union
select 17,'saf','DOP',NULL
UNION
select 18,'NO','KIO','a.0'
UNION
select 19,'NO','KIO','b'
UNION
select 20,'NO','KIO','c.1'
UNION
select 21,'NO','KIO','c.2'
select code,max (revision) from jl
group by code
select * from jlselect a.* from
jl a,
(
select code ,max(ID) as id,max(NAME) as name,max(revision) as a from jl
group by code) b
where a.id=b.id and a.name=b.name and a.code=b.code
insert into jl
select 12,'li','ABC','a.0'
union
select 13,'li','ABC','a.1.0'
union
select 14,'li','ABC','a'
union
select 15,'li','ABC','b.0.0'
union
select 16,'li','ABC','b'
union
select 17,'saf','DOP',NULL
UNION
select 18,'NO','KIO','a.0'
UNION
select 19,'NO','KIO','b'
UNION
select 20,'NO','KIO','c.1'
UNION
select 21,'NO','KIO','c.2'
select code,max (revision) from jl
group by code
select * from jlselect a.* from
jl a,
(
select code ,max(ID) as id,max(NAME) as name,max(revision) as a from jl
group by code) b
where a.id=b.id and a.name=b.name and a.code=b.code
jl a,
(
select code ,max(ID) as id,max(NAME) as name,max(revision) as a from jl
group by code) b
where a.id=b.id and a.name=b.name and a.code=b.code
left(max(rtrim(revision)+'z'),len(max(rtrim(revision)+'z'), )-1) from 表名
insert #aa
select 12 , 'li' , 'ABC', 'a.0'
union all select 13, 'li', 'ABC', 'a.1.0'
union all select 14 , 'li' , 'ABC' , 'a'
union all select 15 , 'li' , 'ABC' , 'b.0.0'
union all select 16 ,'li' , 'ABC' , 'b'
union all select 17, 'Saf', 'DOP' ,''
union all select 18 , 'NO' , 'KIO', 'a.0'
union all select 19 , 'NO' , 'KIO' , 'b'
union all select 20 ,'NO' ,'KIO' , 'c.1'
union all select 21 ,'NO' ,'KIO' , 'c.2'
select * from #aaselect a.* from #aa a,
(select code,Revision=left(Revision,len(Revision)-4) from
(
select code,Revision=max(Revision+'.9.9') from #aa group by code
) tmp) b where a.code=b.code and a.Revision=b.Revision
left(max(rtrim(revision))+'z',len(max(rtrim(revision))+'z') -1) from 表名
jl a,
(
select code ,
max(cast(replace(stuff(isnull(revision,' '),1,1,1),'.','')) as bigint) as a from jl
group by code) b
where a.id=b.id and a.code=b.code
select distinct a.* from
jl a,
(
select code ,
max(cast(replace(stuff(isnull(revision,' '),1,1,1),'.','')) as bigint) as aa from jl
group by code) b
where (cast(replace(stuff(isnull(revision,' '),1,1,1),'.','')) as bigint)=b.aa and a.code=b.code