create table tab1(sno int, name varchar(30), age int);
insert into tab1 values(1, 'abc1', 22);
insert into tab1 values(2, 'abc2', 23);
insert into tab1 values(3, 'xyz', 28);
insert into tab1 values(4, 'abc3', 26);
insert into tab1 values(5, 'abc4', 25);
select sno, name, age, rank() over (order by sno) as ranking from tab1 where
ranking = trunc((select count(*)/2 from tab1)) + 1; //This query is giving error
错误为 ORA-00904:"排名":标识符无效
您正在尝试根据完成所有筛选后计算的值筛选所选结果。您需要将其更改为如下所示的内容:
select * from (
select sno, name, age, rank() over (order by sno) as ranking from tab1
) where ranking = trunc((select count(*)/2 from tab1)) + 1;
你的问题是谢谢你在select
中定义一个别名,然后你想按它过滤。 您需要使用 CTE 或子查询:
with cte as (
select sno, name, age, rank() over (order by sno) as ranking
from tab1
)
select cte.*
from cte
where cte.ranking = trunc((select count(*)/2 from tab1)) + 1;
您似乎想计算中值。 我绝对不会为此目的推荐rank()
,因为它对待关系的方式。 更好的选择是row_number()
,所以这非常接近获得中位数。 而且,您不需要子查询:
with cte as (
select sno, name, age, row_number() over (order by sno) as ranking,
count(*) over () as cnt
from tab1
)
select cte.*
from cte
where 2*cte.ranking in (cnt, cnt + 1)
这对于偶数行和奇数行都足够有效。
您可能还对MEDIAN()
、PERCENTILE_DISC()
和PERCENTILE_CONT()
函数感兴趣。