连接两个表,仅使用右表的最新值 [英] Join two tables, only use latest value of right table
问题描述
我正在尝试联接2个表,但仅与一组记录中的最新记录联接.
I am trying to join 2 tables, but only join with the latest record in a group of records.
左表:
零件
- Part.PartNum
右表:
材料
- Material.Partnum
- Material.Formula
- Material.RevisionNum
修订号从"A"开始并增加.
The revision number starts at "A" and increases.
我想加入PartNum的2个表,但是只合并右边表中的最新记录.我已经看到了有关SO的其他示例,但是很难将它们放在一起.
I would like to join the 2 tables by PartNum, but only join with the latest record from right table. I have seen other examples on SO but an having a hard time putting it all together.
我发现第一个修订版本号是"New",然后它递增A,B,...它永远不会超过一个或两个修订版本,因此我不必担心遍历该序列.但是,如何选择以"New"作为第一个修订版本号的最新版本?
I found out the first revision number is "New", then it increments A,B,... It will never be more than one or two revisions, so I am not worried about going over the sequence. But how do I choose the latest one with 'New' being the first revision number?
推荐答案
将运行此常规SQL语句为:
A general SQL statement that would run this would be:
select P.PartNum, M.Formula, M.RevisionNum
from Parts P
join Material M on P.PartNum = M.PartNum
where M.RevisionNum = (select max(M2.RevisionNum) from Material M2
where M2.PartNum = P.PartNum);
重复以上有关第26版修订之后的注意事项. max(RevisionNum)可能会中断,具体取决于#26之后发生的情况.
Repeating the above caveats about what happens after Revision #26. The max(RevisionNum) may break depending upon what happens after #26.
如果RevisionNum序列始终从w/NEW开始,然后以A,B,C等继续,则需要替换w()并添加一些更复杂(且混乱)的东西:
If RevisionNum sequence always starts w/ NEW and then continues, A, B, C, etc., then the max() needs to be replaced w/ something more complicated (and messy):
select P.PartNum, M.RevisionNum
from Parts P
join Material M on P.PartNum = M.PartNum
where (
(select count(*) from Material M2
where M2.PartNum = P.PartNum) > 1
and M.RevisionNum = (select max(M3.RevisionNum) from Material M3
where M3.PartNum = P.PartNum and M3.RevisionNum <> 'NEW')
)
or (
(select count(*) from Material M4
where M4.PartNum = P.PartNum) = 1
and M.RevisionNum = 'NEW'
)
必须有一种更好的方法来执行此操作.尽管这行得通-必须考虑一个更快的解决方案.
There must be a better way to do this. This works though -- will have to think about a faster solution.
SQL Fiddle: http://sqlfiddle.com/#!3/70c19/3
SQL Fiddle: http://sqlfiddle.com/#!3/70c19/3
这篇关于连接两个表,仅使用右表的最新值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!