最后一条记录在 DB 中查找每个重复的字段 (Oracle 查询)

标签: sql Oracle
发布时间: 2017/4/14 23:53:14
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

Hy 的家伙。 我有一个查询︰

select *
  from stat.cause_code_descriptions 
 INNER JOIN stat.stat_dial 
    on stat.cause_code_descriptions.CODE = stat.stat_dial.cause
 where called like '%3623.1348' 
   and begdt > sysdate-500 
   and rownum <=100 
 order by begdt desc

此查询返回的东西那样

login     code     called        begdtu(unix timestamp)

oeanwel     4   VLAN:3623.1348  1336383493
oe192034    0   VLAN:3623.1348  1336382883
oe192034    2   VLAN:3623.1348  1336382640
oe192034    45  VLAN:3623.1348  1336380257
oeanwel     4   VLAN:3623.1348  1336379883
oe220850    20  VLAN:3623.1348  1336378666
oe194752    4   VLAN:3623.1348  1336378507
oeanna2510  45  VLAN:3623.1348  1336377516
oeanwel     4   VLAN:3623.1348  1336376273
oe237185    45  VLAN:3623.1348  1336374506
oe237185    4   VLAN:3623.1348  1336372662
oe237185    3   VLAN:3623.1348  1336370819
oe239364    3   VLAN:3623.1348  1336367329
oeanna2510  45  VLAN:3623.1348  1336366115

自己在寻找什么的是返回 last(freshest) 记录的任何 login 。 不重复记录我查询的工作很好,但为 oe192034oe237185 登录它将显示所有记录。 我试过 group bydistinct ,但它不工作。请帮助

解决方法 1:

你需要一个窗口函数,方式对工作簿︰

select * 
from 
 (
   select ccd.*, sd.*, row_number() over (partition by login order by begdtu desc) rn
   from stat.cause_code_descriptions ccd
   INNER JOIN stat.stat_dial sd
      on ccd.CODE = sd.cause
   where called like '%3623.1348' 
 ) dt
where rn = 1 
order by begdt desc
上一篇
官方微信
官方QQ群
31647020