如何查找几个字段中排行第N的值?例如同一行数据中有7个字段A、B、C、D、E、F、G,对应的值分别为4、3、6、8、7、5、8;
想找出这7个字段由大到小排列的第6个值,如例子中由大到小排列为8、8、7、6、5、4、3,第6个值则是4.在网上找到取最大用greatest,但不知道怎么求第N个值。
想找出这7个字段由大到小排列的第6个值,如例子中由大到小排列为8、8、7、6、5、4、3,第6个值则是4.在网上找到取最大用greatest,但不知道怎么求第N个值。
(
select * , rownumber() over(order by a desc) px from
(
select a from tb
union all
select b from tb
union all
...
select g from tb
) t
) m
where px = 6
如果你是判断每行的话,最好有个行号.假设为ID。select * from
(
select * , row_number() over(partition by id order by a desc) px from
(
select a from tb
union all
select b from tb
union all
...
select g from tb
) t
) m
where px = 6一楼中rownumber() --> row_number()
2 select 4 A,3 B,6 C,8 D,7 E,5 F,8 G FROM DUAL
3 union all
4 select 1,2,3,4,5,6,7 from dual)
5 select rm 行号,rn 列序,a 值 from
6 ( SELECT t.*,row_number() over (partition by rm order by a desc) rn FROM
7 (SELECT rownum rm, A FROM tb
8 union all
9 select rownum rm, b from tb
10 union all
11 select rownum rm, c from tb
12 union all
13 select rownum rm,d from tb
14 union all
15 select rownum rm,e from tb
16 union all
17 select rownum rm,f from tb
18 union all
19 select rownum rm,G from tb) t
20 )
21* where rn=&colnumseq --输入你要查找的排序后第N列的值
SQL> /
输入 colnumseq 的值: 2
原值 21: where rn=&colnumseq
新值 21: where rn=2 行号 列序 值
---------- ---------- ----------
1 2 8
2 2 6SQL> /
输入 colnumseq 的值: 3
原值 21: where rn=&colnumseq
新值 21: where rn=3 行号 列序 值
---------- ---------- ----------
1 3 7
2 3 5
select * from (
select A, rownum Rnum from (
with tab as(
select 4 as A from dual
union all
select 3 from dual
union all
select 6 from dual
union all
select 8 from dual
union all
select 7 from dual
union all
select 5 from dual
union all
select 8 from dual
)
select A from tab order by A
) t
) t2 where Rnum = 6
select 4 A,3 B,6 C,8 D,7 E,5 F,8 G FROM DUAL union all
select 1,12,13,14,15,16,17 from dual);
--因为wm_concat在子查询order后聚合时排序不规则(不知道为什么),所以只能使用wm_concat over来解决。
select RID, REGEXP_SUBSTR(max(VAL),'\d+',1,3) --3为第3大值
from
(select RID,wm_concat(VAL) over(partition by RID order by VAL DESC) VAL
from(
select decode(level,1,A,2,B,3,C,4,D,5,E,6,F,7,G) VAL,ROWID RID
from TEST3
connect by level<=7 and connect_by_root ROWID=ROWID
)
)group by RID
select rn,max(value)
from (select A value,rownum rn from tb
union all
select B value,rownum rn from tb
....)
group by rn
(select rownum rn,A,B,C,D,E,F,G from quzhi)
select '第'||rn||'行',A VALUE
from (
select row_number()over(partition by rn order by A desc ) rk,rn,A
from (
select rn,A from cte
union all
select rn,B from cte
union all
select rn,C from cte
union all
select rn,D from cte
union all
select rn,E from cte
union all
select rn,F from cte
union all
select rn,G from cte
)k ) kk
where rk = #输入 num 的值: 6
原值 21: where rk = &num
新值 21: where rk = 6'第'||RN||'行' VALUE
-------------------------------------------- ----------
第1行 2
第2行 3
第3行 3
select id,rownum from (select id, rownum from table_name where rownum<100 order by id) where rownum <1
(
select 4 a,3 b,6 c,8 d,7 e,5 f,8 g from dual
)
select * from (
select AA,row_number() over(order by AA desc) px from (
select a AA from tb union all
select b from tb union all
select c from tb union all
select d from tb union all
select e from tb union all
select f from tb union all
select g from tb )
)
where px=6