-- 自己写个带参考的my_min()函数,就传两个数值,返回最小的,然后: create or replace function my_min(i_a number, i_b number) return number is begin if i_a<i_b then return i_a; else return i_b; end if; exception when others then return null; end; / -- 假设你的表四个字段分别是:col_a、col_b、col_c、col_d select my_min( (case when col_a>col_b then col_a else col_b end), (case when col_c>col_d then col_c else col_d end) ) from table_name;
create table #t(id int) goinsert into #t select 2 insert into #t select 3 insert into #t select 4 insert into #t select 5select top 1 ID from( select top 2 ID from #t order by id desc ) t order by t.id ------------- 4
-- 自己写个带参考的my_min()函数,就传四个数值,返回第二小的: create or replace function second_max(i_a number, i_b number, i_c number, i_d number) return number is v_return number; begin 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; return v_return; exception when others then return null; end; /-- 假设你的表四个字段分别是:col_a、col_b、col_c、col_d select col_a, col_b, col_c, col_d, second_max(col_a,col_b,col_c,col_d) from tb;
scott@TBWORA> create or replace function second_max(i_a number, i_b number, i_c number, i_d number) 2 return number 3 is 4 v_return number; 5 begin 6 with a as( select i_a as vl from dual 7 union all 8 select i_b as vl from dual 9 union all 10 select i_c as vl from dual 11 union all 12 select i_d as vl from dual ), 13 b as(select row_number() over(order by a.vl desc) as cno, vl from a) 14 select b.vl into v_return from b where b.cno=2; 15 return v_return; 16 exception when others then 17 return null; 18 end; 19 /函数已创建。scott@TBWORA> select col_a, col_b, col_c, col_d, second_max(col_a,col_b,col_c,col_d) from tb; COL_A COL_B COL_C COL_D SECOND_MAX(COL_A,COL_B,COL_C,COL_D) ---------- ---------- ---------- ---------- ----------------------------------- 5 4 3 2 4
复杂化了吧?select max(x) from (select b.x from table b where x < (select max(c.x) from table c) )
-- 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;
想复杂的那个才给力呢! 楼主的要求实现方式很多,随便参考一个吧! 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
先排序 取第二个值 或者用分析函数 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;
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);
用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;
忘了加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;
select x from (select distinct x from A order by x desc ) where rownum=2
笨了,加什么大于1小于3,直接就是等于2SELECT t.X FROM (SELECT X,ROW_NUMBER() OVER(ORDER BY X DESC) num FROM A) t WHERE t.num=2;
我说一下 我在这个问题上的成长吧 。 --很久以前用的方法 --反查 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 ;
select a.x from (select x from a order by x desc) a where a.rownum=2;
select min(x) from (select a.x from (select x from a order by x desc) a where rownum<=2);
select max(X)from A where <>(select max(X) from A)
有2条数据以上的情况 select max(X)from A where X<>(select max(X) from A)
最简单的办法,先取出最大值max,然后根据条件不等于最大值的倒序排列(再取最大值)然后select a from table where a != max order by a desc(假设a即 某字段)
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
create or replace function my_min(i_a number, i_b number)
return number
is
begin
if i_a<i_b then
return i_a;
else
return i_b;
end if;
exception when others then
return null;
end;
/
-- 假设你的表四个字段分别是:col_a、col_b、col_c、col_d
select my_min( (case when col_a>col_b then col_a else col_b end),
(case when col_c>col_d then col_c else col_d end) ) from table_name;
create table #t(id int)
goinsert into #t select 2
insert into #t select 3
insert into #t select 4
insert into #t select 5select top 1 ID
from(
select top 2 ID from #t
order by id desc
) t
order by t.id
-------------
4
create or replace function second_max(i_a number, i_b number, i_c number, i_d number)
return number
is
v_return number;
begin
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;
return v_return;
exception when others then
return null;
end;
/-- 假设你的表四个字段分别是:col_a、col_b、col_c、col_d
select col_a, col_b, col_c, col_d, second_max(col_a,col_b,col_c,col_d) from tb;
2 return number
3 is
4 v_return number;
5 begin
6 with a as( select i_a as vl from dual
7 union all
8 select i_b as vl from dual
9 union all
10 select i_c as vl from dual
11 union all
12 select i_d as vl from dual ),
13 b as(select row_number() over(order by a.vl desc) as cno, vl from a)
14 select b.vl into v_return from b where b.cno=2;
15 return v_return;
16 exception when others then
17 return null;
18 end;
19 /函数已创建。scott@TBWORA> select col_a, col_b, col_c, col_d, second_max(col_a,col_b,col_c,col_d) from tb; COL_A COL_B COL_C COL_D SECOND_MAX(COL_A,COL_B,COL_C,COL_D)
---------- ---------- ---------- ---------- -----------------------------------
5 4 3 2 4
(select b.x
from table b
where x < (select max(c.x)
from table c)
)
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;
楼主的要求实现方式很多,随便参考一个吧!
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
select b.vl into v_return from b where b.cno=2;
(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);
(select distinct x
from A
order by x desc
)
where rownum=2
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 ;
select a.x from (select x from a order by x desc) a where a.rownum=2;
select max(X)from A where X<>(select max(X) from A)
(
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