求某列第二最大值所在行的SQL

phoenix_zg 2015-08-19 11:44:56
如题,想获取某列最大值所在行,以及该列次最大值所在行。因为海量数据的原因,不想用排序。不知道可不可以用exists实现?
C1 SEQ
a 1
a 3
a 5
b 1
b 2
b 3


想要的结果
C1 SEQ
a 3
b 2

或者能出来这样的结果也行
C1 SEQ
a 3
a 5
b 2
b 3
...全文
392 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
越烟 2015-09-09
  • 打赏
  • 举报
回复
我用 emp 表来说一下把 。。。 select * from ( select p.*, dense_rank() over( partition by deptno order by sal desc) d from scott.emp p) p where p.d=2; 如果是11g 的话用 select * from ( select p.*, nth_value(sal,2) from last over( partition by deptno order by sal rows between unbounded preceding and unbounded following ) p from scott.emp p ) where sal = p
惑先生 2015-08-29
  • 打赏
  • 举报
回复
WITH T AS
(SELECT 'a' AS C1, 1 AS SEQ
FROM DUAL
UNION ALL
SELECT 'a' AS C1, 3 AS SEQ
FROM DUAL
UNION ALL
SELECT 'a' AS C1, 5 AS SEQ
FROM DUAL
UNION ALL
SELECT 'b' AS C1, 1 AS SEQ
FROM DUAL
UNION ALL
SELECT 'b' AS C1, 2 AS SEQ
FROM DUAL
UNION ALL
SELECT 'b' AS C1, 3 AS SEQ FROM DUAL)
SELECT C1, MAX(SEQ) AS SEQ
FROM T
GROUP BY C1
UNION
SELECT T.C1, MAX(T.SEQ) AS SEQ
FROM T,(
SELECT C1, MAX(SEQ) AS SEQ
FROM T
GROUP BY C1) TT
WHERE T.C1 = TT.C1 AND T.SEQ < TT.SEQ
GROUP BY T.C1


这种写法效率比排序还差
kingwinerxp 2015-08-27
  • 打赏
  • 举报
回复
如果是11gR2的话,可以考虑用Nth_value
mayanzs 2015-08-27
  • 打赏
  • 举报
回复
需: order by seq desc 要不然是第二最小了。
卖水果的net 2015-08-27
  • 打赏
  • 举报
回复
如果数据的重复的,可以使用 dense_rank() 代替 row_number()
xu176032 2015-08-20
  • 打赏
  • 举报
回复

create table t2(c1 varchar2(30),seq varchar2(30));

insert into t2 values('a','1');
insert into t2 values('a','3');
insert into t2 values('a','5');
insert into t2 values('b','1');
insert into t2 values('b','2');
insert into t2 values('b','3');



select c1, seq
  from (select t2.*, row_number() over(partition by c1 order by seq) as fn
          from t2)
 where fn = 2;

select c1, seq
  from (select t2.*, row_number() over(partition by c1 order by seq) as fn
          from t2)
 where fn >= 2;



3,490

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧