指定行数范围查询

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

 

转载于:https://www.cnblogs.com/fushou/p/8416329.html