现在有若干个表(表结构相同Id越大版本号越高),例如表A,B表A:
VersionId VersionNumber CreatedDate ProjectName
1 1.0.0.0 6/28/2008 A
2 1.0.20.1 6/29/2008 A
3 1.0.30.2 6/30/2008 A
表B:
VersionId VersionNumber CreatedDate ProjectName
1 2.0.423.0 6/22/2008 B
2 2.0.567.1 6/23/2008 B
3 2.0.986.2 6/24/2008 B现在要获得这样的结果,也就是获得每个Project的最新版本ProjectName VersionNumber CreatedDate
A 1.0.30.2 6/30/2008
B 2.0.986.2 6/24/2008 我开始是相拥order by 先查出最高版本再用union all 合并的但是这样是不符合sql语法的
只能用临时表解决嘛?
请大家帮忙 谢谢
VersionId VersionNumber CreatedDate ProjectName
1 1.0.0.0 6/28/2008 A
2 1.0.20.1 6/29/2008 A
3 1.0.30.2 6/30/2008 A
表B:
VersionId VersionNumber CreatedDate ProjectName
1 2.0.423.0 6/22/2008 B
2 2.0.567.1 6/23/2008 B
3 2.0.986.2 6/24/2008 B现在要获得这样的结果,也就是获得每个Project的最新版本ProjectName VersionNumber CreatedDate
A 1.0.30.2 6/30/2008
B 2.0.986.2 6/24/2008 我开始是相拥order by 先查出最高版本再用union all 合并的但是这样是不符合sql语法的
只能用临时表解决嘛?
请大家帮忙 谢谢
union all
select * from B x where CreatedDate=(select max(CreatedDate) from B where ProjectName=x.ProjectName )
with cte as(
select *,rowid=row_number() over (partition by projectname order by versionid desc )from (
Select * from #A union all
Select * from #B)b
)
select * from cte where rowid=1
VersionId VersionNumber CreatedDate ProjectName
1 2.0.423.0 6/22/2008 B
2 2.0.567.1 6/23/2008 B
3 2.0.986.2 6/24/2008 B
4 2.0.999.2 6/24/2008 B
select * from A x where CreatedDate=(select max(ID) from A where ProjectName=x.ProjectName )
union all
select * from B x where CreatedDate=(select max(ID) from B where ProjectName=x.ProjectName )