Oracle组的一部分行并获取最新的时间戳 [英] Oracle group part of row and get row with latest timestamp

问题描述

我有一个select给了我这个结果：

   -  ID |系统| Type1 | NID | Name_ | Type2__ |日期
  -  24 | AA-Tool | PRIV | 816 | Name1 | IMPLICIT | 17.12.2014 
  -  24 | AA-Tool | PRIV | 816 | Name1 |显式| 19.12.2014 
  -  24 | AA-Tool | PRIV | 816 | Name1 |显式| 20.12.2014 
  -  25 | BB-Tool | PRIV | 817 | Name2 |显式| 20.12.2014 
  -  25 | BB-Tool | PRIV | 817 | Name2 |显式| 21.12.2014 
  

因此，ID，System，Type1，NID和Name应该是不同的，并且Type2和Date应该成为日期的最后一个条目..
这应该是结果：

   -  24 | AA-Tool | PRIV | 816 | Name1 |显式| 20.12.2014 
  -  25 | BB-Tool | PRIV | 817 | Name2 |显式| 21.12.2014 
  

我希望这是可以理解的：）

谢谢，
Michael

解决方案

试试这个：

  SELECT a.ID，a.System，a.Type1，a.NID，a.Name_，a.Type2__，a.Date 
 FROM tableA a 
 INNER JOIN（SELECT a.ID，a.System，a.Type1，a.NID，a.Name_，MAX（a.Date）日期
 FROM tableA a 
 GROUP BY a.ID，a .System，a.Type1，a.NID，a.Name_ 
）b ON a.ID = b.ID AND a.System = b.System AND 
 a.Type1 = b.Type1 AND a .NID = b.NID和
 a.Name_ = b.Name_ AND a.Date = b.Date; 
  

I have a select that gives me this result:

 - ID   | System    | Type1 | NID       | Name_ | Type2__   | Date 
 - 24   | AA-Tool   | PRIV  | 816       | Name1 | IMPLICIT  | 17.12.2014
 - 24   | AA-Tool   | PRIV  | 816       | Name1 | EXPLICIT  | 19.12.2014
 - 24   | AA-Tool   | PRIV  | 816       | Name1 | EXPLICIT  | 20.12.2014
 - 25   | BB-Tool   | PRIV  | 817       | Name2 | EXPLICIT  | 20.12.2014
 - 25   | BB-Tool   | PRIV  | 817       | Name2 | EXPLICIT  | 21.12.2014

So ID, System, Type1, NID and Name should be distinct and Type2 and Date should be the last entry by date.. This should be the result:

 - 24   | AA-Tool   | PRIV  | 816       | Name1 | EXPLICIT  | 20.12.2014
 - 25   | BB-Tool   | PRIV  | 817       | Name2 | EXPLICIT  | 21.12.2014

I hope thats understandable :)

Thanks, Michael

解决方案

Try this:

SELECT a.ID, a.System, a.Type1, a.NID, a.Name_, a.Type2__, a.Date
FROM tableA a 
INNER JOIN (SELECT a.ID, a.System, a.Type1, a.NID, a.Name_, MAX(a.Date) Date 
            FROM tableA a
            GROUP BY a.ID, a.System, a.Type1, a.NID, a.Name_
           ) b ON a.ID = b.ID AND a.System = b.System AND 
                  a.Type1 = b.Type1 AND a.NID = b.NID AND 
                  a.Name_ = b.Name_ AND a.Date = b.Date;

这篇关于Oracle组的一部分行并获取最新的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！