由于LIMIT字段是MySQL用来分页显示数据的,所以今天就用ROWNUM来实现ORACLE分页,这里值得强调的是:ROWNUM不支持大于号(>),所以在进行数据区间查询时,需要嵌套。
如每页展示10条数据记录,若要显示第二页的数据,则SQL语句如下:
SELECT * FROM (SELECT ROWNUM rn,t.* FROM TEST_USER_INFO t WHERE ROWNUM < 20 ) n WHERE n.rn > 10
自己写了一段联表查询,再进行分页,深刻体会到MySQL的轻量级的好处了。。。
SELECT* FROM (SELECT ROWNUM rn,t.* FROM (SELECT DISTINCT a.USER_ID,a.EMPLOYEE_NAME,a.USER_LOGIN_NAME,a.MOBILE_PHONE,a.AREAID,a.COUNTYID
FROM V_USER_INFO a
LEFT JOIN AI_SYS_USER_2_ROLE b ON a.USER_ID=b.USER_ID AND b.DATA_STATUS='1'
LEFT JOIN AI_SYS_ROLE c ON b.ROLE_ID = c.ROLE_ID AND c.ROLE_STATUS='1' WHERE c.ROLE_LEVEL NOT IN ('01','02','03') AND a.AREAID = 'B' ORDER BY a.USER_ID ASC) t WHERE ROWNUM <= 10) n WHERE n.rn > 5