Oracle sql 去掉白分之十的最小值和百分之十的最大值，rownum的用法

Question

代码段

 WITH P AS (  select m.Departure_Airport || '-' || m.Arrival_Airport line  , a.Aircraft_Type, '2021-04' m,
                    Avg(NVL(t.Final_Takeoff_Fuel - t.Final_Landing_Fuel,0)) ff, 
                    m.departure_airport ,m.arrival_airport, 1 x, 1 xx, '', 'admin' admin, 'admin' admin1, 
                    sysdate cd, sysdate ed, a.company_code
                    from ta_fli_matchtable m 
                    left join tm_omd_aircraft a on m.aircraft_code = a.aircraft_code 
                    left join Ta_Tif_Fuelinfo t on m.Match_Code = t.Match_Code
                    where  m.flight_date > to_date('2021-04-01', 'yyyy-MM-dd') 
                    and m.flight_date < to_date('2021-04-30', 'yyyy-MM-dd') 
                    and a.company_code = '8Y'                 
                    group by m.departure_airport, m.arrival_airport, a.aircraft_type, a.company_code 
                    order by ff)                 
 SELECT p.* FROM p where  ROWNUM>(select round(count(*)*0.1) from p) and ROWNUM<(select round(count(*)*0.9) from p)
                    
       

这个语句无法查出结果，如果没有where条件是能查到结果的，求解惑rownum的用法

Answer 1

select * from(select rownum no ,id,name from student) where no>2; rownum作为子集操作就可以了