指定行数范围查询
1、行范围超出总行数,也能查到总行数记录
SELECT res.* from (SELECT t.*, COALESCE(max(RN) OVER(),0) as TOTAL FROM (SELECT LOG_ID, OP_SYS,OP_USER,TO_CHAR(OP_TIME,'yyyy-mm-dd hh24:mi:ss') as OP_TIME, OP_TYPE, OP_OBJECT,COALESCE(FACTORY_EQUIPMENT,''),COALESCE(SUB_VERSION,''),COALESCE(MARKET_CODE,''), COALESCE(ICS_TYPE,''),COALESCE(EFFECTIVE_DATE,''),COALESCE(EXPIRATION_DATE,''), ROWNUMBER() OVER( ORDER BY LOG_ID DESC ) AS RN FROM TM_TBALE_LOG WHERE AIRLINE='CA' AND OP_OBJECT='E' UNION ALL SELECT null,null,null,null,null,null,null,null,null,null,null,null,null from SYSIBM.SYSDUMMY1) as t) as res WHERE res.RN BETWEEN 11 AND 12 OR res.RN IS NULL
2、行范围超出总行数,不能查到总行数记录
SELECT * FROM ( SELECT LOG_ID, OP_SYS,OP_USER,TO_CHAR(OP_TIME,'yyyy-mm-dd hh24:mi:ss') as OP_TIME, OP_TYPE, OP_OBJECT,OP_ID, CLOB(EQUIPMENT_JSON),AIRLINE,FACTORY_EQUIPMENT,SUB_VERSION,MARKET_CODE,ICS_TYPE,EFFECTIVE_DATE,EXPIRATION_DATE, ROWNUMBER() OVER( ORDER BY LOG_ID DESC ) AS RN, COUNT(*) OVER() TOTAL FROM TM_MSM_TABLE_LOG WHERE OP_TIME >='2017-09-21' and OP_TIME <='2017-12-31' AND OP_OBJECT='M') AS t WHERE t.RN BETWEEN 1 and 6