SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( SELECT c1, c2, count(*) over () total -----依靠此句查出 数量总和 FROM TABLE_NAME ) A WHERE ROWNUM <= 40 )WHERE RN >= 21
例子:sql
SELECT T.* FROM ( SELECT ROWNUM AS ROWNO, R.* FROM( SELECT N.NRW_PER, TO_CHAR( N.MDATE ,'YYYY-MM' ) MDATE ,N.OTITLE, N.OID, N.CLSID, N. HB_PER ,L.LOST_PER LOST_PER , L.HB_PER LOST_HB_PER , L.LOST_POWER LOST_POWER ,S.MFLOW AS S_MFLOW ,G.MFLOW AS G_MFLOW ,COUNT(*) OVER () total FROM DW_NRW_MONTH N LEFT JOIN DW_LOST_MONTH L ON L.MDATE = N.MDATE AND L.OID = N.OID LEFT JOIN DW_SS_MONTH S ON S.MDATE = N.MDATE AND S.OID = N.OID LEFT JOIN DW_GS_MONTH G ON S.MDATE = N.MDATE AND G.OID = N.OID WHERE 1=1 AND N.NRW_PER >=0 AND N.NRW_PER <=30 --区间 AND N.MDATE >= TO_DATE('2018-01-01','YYYY-MM-DD') --开始时间 AND N.MDATE <= TO_DATE('2018-01-02','YYYY-MM-DD') --结束时间 AND Exists (SELECT MP_CHILDREN FROM dw_md_object_map_index WHERE MP_PARENT =2 and MP_CHILDREN = N.OID ) --父子级关系 ORDER BY MDATE ) R WHERE ROWNUM <= 20 ) T WHERE T.ROWNO >= 5
结果:code