怎么取得某个字段的第二大的值

[matt1014]

比如A表有字段X,假设它有四个值5，4，3，2。我要的结果就是4

[luoyoumou]

-- 哦， 我还以为是同一条记录的4个字段平行比较，求第二大值呢!

-- 看来：我想复杂啦!

[luoyoumou]

-- 4个给定值的第二大值，就我这个思路：
with a as( select i_a as vl from dual
             union all
             select i_b as vl from dual
             union all
             select i_c as vl from dual
             union all
             select i_d as vl from dual ),
       b as(select row_number() over(order by a.vl desc) as cno, vl from a)
  select b.vl into v_return from b where b.cno=2;

[jimmylin040]

用分析函数就可以了。。。

[lxyzxq2008]

[Quote=引用 12 楼 luoyoumou 的回复:]

SQL code
-- 4个给定值的第二大值，就我这个思路：
with a as( select i_a as vl from dual
union all
select i_b as vl from dual
union all
select i_c as vl from dual
……
[/Quote]
想复杂的那个才给力呢！
楼主的要求实现方式很多，随便参考一个吧！
with tab as (select 1 id from dual union all select 2 from dual union all select 3 from dual)
select id from (select id, row_number() over(order by id desc) rt from tab) where rt = 2
---------------
2

[coolkisses]

解决思路同上，先排序，再取第2个值。

[scrack]

先排序 取第二个值 或者用分析函数 select row_number() over(order by a.vl desc) as cno, vl from a)
select b.vl into v_return from b where b.cno=2;

[ding2wife]

受益匪浅啊

[lisong770818]

with atest as
(select 3 x
from dual
union all
select 2 x
from dual
union all
select 5 x
from dual
union all
select 1 x
from dual
union all
select 4 x from dual)

select max(t.x) from atest t where t.x not in (select max(x) from atest);

[寻道的 Programmer]

用over函数

SELECT t.X FROM (SELECT X,ROW_NUMBER() OVER(ORDER BY X) num FROM A) t WHERE t.num>1 AND t.num<3;

[寻道的 Programmer]

忘了加DESC了

SELECT t.X FROM (SELECT X,ROW_NUMBER() OVER(ORDER BY X DESC) num FROM A) t WHERE t.num>1 AND t.num<3;

[townsin]

select x from
(select distinct x
from A
order by x desc
)
where rownum=2

[寻道的 Programmer]

笨了，加什么大于1小于3，直接就是等于2

SELECT t.X FROM (SELECT X,ROW_NUMBER() OVER(ORDER BY X DESC) num FROM A) t WHERE t.num=2;

[opps_zhou]

...这个居然码到 20 多楼...

[d604060189]

我说一下 我在这个问题上的成长吧 。

  --很久以前用的方法 --反查
with temp_tab1 as 
(select '1' as num1 from dual union all 
select '2' as num1 from dual union all 
select '3' as num1 from dual union all 
select '4' as num1 from dual union all 
select '5' as num1 from dual )
select max(num1) from temp_tab1 where num1 <(select max(num1) from temp_tab1)
--后来会分析函数了
with temp_tab1 as 
(select '1' as num1 from dual union all 
select '2' as num1 from dual union all 
select '3' as num1 from dual union all 
select '4' as num1 from dual union all 
select '5' as num1 from dual )
select num1 from (select num1 , row_number()over(order by num1 desc) rn from temp_tab1 )  where rn = 2 ;
--如果有重复记录 就用 dense_rank 后来会分析函数了
with temp_tab1 as 
(select '1' as num1 from dual union all 
select '2' as num1 from dual union all 
select '5' as num1 from dual union all 
select '3' as num1 from dual union all 
select '4' as num1 from dual union all 
select '4' as num1 from dual union all 
select '5' as num1 from dual )
select * from (select num1 , dense_rank()over(order by num1 desc) rn from temp_tab1 )  where rn = 2 ;

[xijiaoda_liuhao]

select a.x from (select x from a order by x desc) a where a.rownum=2;

[hbygxx]

最简单的办法，先取出最大值max，然后根据条件不等于最大值的倒序排列（再取最大值）

然后select a from table where a != max order by a desc（假设a即 某字段）

[xiaojiushao77]

with tmp as
(
select 3 as num from dual union all
select 4 as num from dual union all
select 5 as num from dual union all
select 2 as num from dual
)
select num from (select rownum as id,num from tmp) where id = 2

[fzqrf]

select min(x) from (select a.x from (select x from a order by x desc) a where rownum<=2);

[xpingping]

select max(X)from A where <>(select max(X) from A)

[xpingping]

有2条数据以上的情况
select max(X)from A where X<>(select max(X) from A)