时间戳

首页 > 解决方案 > 从最后一个可用值 SQL 中获取最新信息

sql - 从最后一个可用值 SQL 中获取最新信息

问题描述

我有两张桌子

掌握

MasterID PaperID Version PaperName
1        ISBN-1X 1       Medical Research
2        ISBN-1X 2       Medical Research(2)
3        ISBN-1X 3       Medical Research(2.1)
4        ISBN-2X 1       Some Research(1.0)
5        ISBN-3X 1       Unpublished Research(0.0)

论文作者

MasterId AuthorName
1        ABC
2        XYZ
4        LMN

注意：作者表只有在与以前版本的作者不同时才会获得条目。否则纸质版本会在 master 中更新，作者保持不变。

如何获得最后更新作者的最新版本论文

预期结果

MasterID PaperID Version PaperName                  AuthorName
3        ISBN-1X 3       Medical Research(2.1)      XYZ
4        ISBN-2X 1       Some Research(1.0)         LMN
5        ISBN-3X 1       Unpublished Research(0.0)

我的尝试：

Select M.MasterID, M.PaperID ,M.Version, M.PaperName, A.AuthorName
From Master M 
Inner Join 
(select  PaperID , max(version) maxversion from Master group by PaperID ) M1 
on m1.PaperID  = M.PaperID   AND M1.maxversion = M.Version
left join PaperAuthor A on M.MasterID  = A.MasterID

演示

标签： sqlsql-server

解决方案

试试下面的row_number，这里是演示。

;with mst as
(
  select
    *,
    row_number() over (partition by PaperID order by version desc) as rnk
  from master
),
ppauth as
(
  select 
    *,
    row_number() over (order by MasterID desc) as rnk
  from PaperAuthor
)


select
  m.MasterID,
  PaperID,
  Version,
  PaperName,
  AuthorName
from mst m
join ppauth p
on m.rnk = p.rnk
where m.rnk = 1

输出：

-------------------------------------------------------------
| MasterID  PaperID  Version     PaperName        AuthorName|
-------------------------------------------------------------
|  3        ISBN-1X    3    Medical Research(2.1)    XYZ    | 
-------------------------------------------------------------

推荐阅读