woshizhuyoulei 于 2016.09.10 21:20 提问

oracle中用一个sql实现要求

6个回答

zhuzige521888   2016.09.10 21:43

SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ip ORDER BY time DESC) rn, t.* FROM tablename t) WHERE rn = 1 ;

zhuzige521888 =0就是第一条，<=1 就是前两条，<=2，就是前三条

zhuzige521888 create table dtest( dip number, dtime date, dname varchar(20) ) select * from duan a where (select count(1) from duan b where a.dip = b.dip and b.dtime > a.dtime ) <=1 order by dip,dtime;

zhuzige521888 回复哎梦哦彼咯: 多个字段用开窗函数第二个select后加上你想要的字段，或者tablename.*，groupby的不行。

woshizhuyoulei 这样能得到每一个ip的最近一条记录吗

woshizhuyoulei 我想得到一整条记录 这样出来只有两个字段

zhuzige521888 这种也可以：select ip,max(time) from tablename group by ip;

zhuzige521888 oracle进行分组后，取出每组的前几条数据SELECT *FROM (SELECT 分组的字段名, ROW_NUMBER() OVER(PARTITION BY 分组的字段名 ORDER BY 排序的字段名) AS RNFROM 表名)WHERE RN <= 10??

caoxiaohucxh   2016.09.13 21:41

select ip,time,id from （select * from t order by time desc）group by ip

Samme   2016.09.11 19:21

Select max(ip),(Select max(date) From abc b where date=a.date) date
From abc a
Group by ip

qq_23898511   2016.09.12 10:00

----------------------------------------------------------------

select ip,max(atime)maxtime from A group by ip

select A.* from A inner join (select ip,max(atime)maxtime from A group by ip)a2 on A.ip=a2.ip and a.time=a2.maxtime

lpsky0865   2016.09.12 11:26

`````` select * from table a where not exists (
select 1 from table b where a.ip = b.ip and b.rowid > a.rowid
);
``````
yushiyoufight 揣测了

beyon2008   2016.10.19 09:34

select *
from t
where (ip, dt) in (select ip, max(dt) lastdt from t group by ip)