SELECT * FROM (
SELECT A.*, ROWNUM RN FROM (
SELECT
c1,
c2,
count(*) over () total -----依靠此句查出 数量总和
FROM TABLE_NAME
) A WHERE ROWNUM <= 40
)WHERE RN >= 21
例子:
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
结果: