select 线路编号,速率,测试的时间 from tb a where not exists(select 1 from tb b where a.线路编号=b.线路编号 and trunc(a.测试的时间)=trunc(b.测试的时间) and a.速率<b.速率)
参照 select a.deptno,a.empno,b.ename from (select deptno,max(empno) empno from emp group by deptno) a,emp b where a.empno=b.empno; select deptno,empno,ename from (select deptno,ename,empno,row_number()over(partition by deptno order by empno desc) rn from emp) where rn=1;
WITH tabname AS( SELECT 1 线路编号,To_Date('2010-10-02 14:25:00','yyyy-mm-dd hh24:mi:ss') 测试时间, 100 测速速率 FROM dual UNION ALL SELECT 1,To_Date('2010-10-02 15:25:00','yyyy-mm-dd hh24:mi:ss'),150 FROM dual UNION ALL SELECT 1,To_Date('2010-10-03 18:25:00','yyyy-mm-dd hh24:mi:ss'),250 FROM dual UNION ALL SELECT 2,To_Date('2010-10-02 15:25:00','yyyy-mm-dd hh24:mi:ss'),100 FROM dual UNION ALL SELECT 2,To_Date('2010-10-02 16:25:00','yyyy-mm-dd hh24:mi:ss'),380 FROM dual UNION ALL SELECT 2,To_Date('2010-10-03 15:25:00','yyyy-mm-dd hh24:mi:ss'),160 FROM dual UNION ALL SELECT 2,To_Date('2010-10-03 16:25:00','yyyy-mm-dd hh24:mi:ss'),320 FROM dual ) --以上是测试数据: SELECT a.线路编号,a.测试时间,a.测速速率 FROM tabname a, (select 线路编号,Max(测速速率) 最大速率 FROM tabname GROUP BY 线路编号) b WHERE a.线路编号=b.线路编号 AND a.测速速率=b.最大速率--结果: 线路编号 测试时间 测速速率 --------------------------------------------- 1 2010.10.03 18:25:00 250 2 2010.10.02 16:25:00 380
自己写了一个: select node_code,max(rate_info) ,max(update_date) keep (dense_rank first order by rate_info desc) from test t group by node_code
from tb a
where not exists(select 1 from tb b where a.线路编号=b.线路编号 and trunc(a.测试的时间)=trunc(b.测试的时间) and a.速率<b.速率)
select a.deptno,a.empno,b.ename from
(select deptno,max(empno) empno from emp group by deptno) a,emp b
where a.empno=b.empno;
select deptno,empno,ename from
(select deptno,ename,empno,row_number()over(partition by deptno order by empno desc) rn from emp)
where rn=1;
SELECT 1 线路编号,To_Date('2010-10-02 14:25:00','yyyy-mm-dd hh24:mi:ss') 测试时间, 100 测速速率 FROM dual
UNION ALL
SELECT 1,To_Date('2010-10-02 15:25:00','yyyy-mm-dd hh24:mi:ss'),150 FROM dual UNION ALL
SELECT 1,To_Date('2010-10-03 18:25:00','yyyy-mm-dd hh24:mi:ss'),250 FROM dual UNION ALL
SELECT 2,To_Date('2010-10-02 15:25:00','yyyy-mm-dd hh24:mi:ss'),100 FROM dual UNION ALL
SELECT 2,To_Date('2010-10-02 16:25:00','yyyy-mm-dd hh24:mi:ss'),380 FROM dual UNION ALL
SELECT 2,To_Date('2010-10-03 15:25:00','yyyy-mm-dd hh24:mi:ss'),160 FROM dual UNION ALL
SELECT 2,To_Date('2010-10-03 16:25:00','yyyy-mm-dd hh24:mi:ss'),320 FROM dual
)
--以上是测试数据:
SELECT a.线路编号,a.测试时间,a.测速速率 FROM tabname a,
(select 线路编号,Max(测速速率) 最大速率 FROM tabname GROUP BY 线路编号) b
WHERE a.线路编号=b.线路编号 AND a.测速速率=b.最大速率--结果:
线路编号 测试时间 测速速率
---------------------------------------------
1 2010.10.03 18:25:00 250
2 2010.10.02 16:25:00 380
select node_code,max(rate_info)
,max(update_date) keep (dense_rank first order by rate_info desc)
from test t
group by node_code