deal表中存有如下记录:
clientID, ClientName, Log_Date
001, s1,2009-01-01
002,s2,2009-02-01
001,ss1,2009-04-01理论上来说,所有clientID是001的名字都应该相同,但是因为历史人工输入问题,可能造成不一致,我想取出唯一的id,name信息,如果id相同名字不同,则用最近的log_date的名字。怎么写这条语句?谢谢
clientID, ClientName, Log_Date
001, s1,2009-01-01
002,s2,2009-02-01
001,ss1,2009-04-01理论上来说,所有clientID是001的名字都应该相同,但是因为历史人工输入问题,可能造成不一致,我想取出唯一的id,name信息,如果id相同名字不同,则用最近的log_date的名字。怎么写这条语句?谢谢
from
(
select a.*,row_number() over(partition by clientid order by log_date desc) rn
from deal a
)
where
rn=1
from a
from a
group by clientID
from deal d
where exists( select * from (select clientid,max(log_date) log_date from deal group by clientid) t where d.clientid=t.clientid
and d.log_date=t.log_date)
(
select clientid,clientname,log_date,row_number() over(partition by clientid order by log_date desc) rn from deal)
where rn=1
select a.clientID, b. ClientName from deal a, (select Log_Date, clientID, ClientName, row_number() over(partition by clientid order by log_date desc) rn
from deal ) b where a. clientID = b. clientID
select d2.clientID,d2.ClientName
from(
select clientID,
max(Log_Date) mld
from deal group by clientID
)d1, deal d2 where d1.clientID=d2.clientID and d1.mld=d2.Log_Date
最后的d1.mld=d2.Log_Date可以去掉吧select d2.clientID,d2.ClientName
from(
select clientID,
max(Log_Date) mld
from deal group by clientID
)d1, deal d2 where d1.clientID=d2.clientID
insert into deal values(001,'s1',to_date('2009-1-1','yyyy-mm-dd'));
insert into deal values(002,'s2',to_date('2009-4-1','yyyy-mm-dd'));
insert into deal values(001,'ss1',to_date('2009-4-1','yyyy-mm-dd'));
commit;
select * from deal;select clientid, clientname, log_date
from (select t.clientid,
t.clientname,
t.log_date,
row_number() over(partition by t.clientid order by t.log_date desc) rn
from deal t)
where rn = 1;