将运行此常规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