DB2 的分页

最新推荐文章于 2021-06-29 17:35:29 发布

iceman1952

最新推荐文章于 2021-06-29 17:35:29 发布

阅读量897

点赞数

本文链接：https://blog.csdn.net/iceman1952/article/details/8472869

版权

Hibernate 是这么实现的（我怀疑Hibernate不支持 distinct，但是，没有实际试过）

select * from 
    (select rownumber() over(order by AC_CHANGE_TIME desc, AC_REF_NAME, AC_USR_NICK) as PAGINATE_COL_ROWNBR,  AC_CHANGE_TIME, AC_REF_NAME, AC_USR_NICK, AC_ID, AC_CTL_NBR, AC_USR_NBR, AC_USR_NICK, AC_CATEGORY, AC_SECONDARY_CATEGORY, AC_TABNAME, AC_COLNAME, AC_REF_ID_INTEGER, AC_REF_ID_STRING, AC_PROPERTY_NAME, AC_OLD_VALUE, AC_NEW_VALUE, AC_DETAILS  
        from ei.REPLACEDTABLENAME where AC_CTL_NBR = 3295 order by AC_CHANGE_TIME desc, AC_REF_NAME, AC_USR_NICK)
         PAGINATE_TABLE_TMPNAME
where PAGINATE_COL_ROWNBR between ? and ?;

下面的实现支持 distinct 总共需要3个select

select * from 
    (select PAGINATE_TABLE_TMPNAME.*, rownumber() over() as PAGINATE_COL_ROWNBR 
     from (select   AC_CHANGE_TIME, AC_REF_NAME, AC_USR_NICK, AC_ID, AC_CTL_NBR, AC_USR_NBR, AC_USR_NICK, AC_CATEGORY, AC_SECONDARY_CATEGORY, AC_TABNAME, AC_COLNAME, AC_REF_ID_INTEGER, AC_REF_ID_STRING, AC_PROPERTY_NAME, AC_OLD_VALUE, AC_NEW_VALUE, AC_DETAILS  
                            from ei.REPLACEDTABLENAME where AC_CTL_NBR = 3295 order by AC_CHANGE_TIME desc, AC_REF_NAME, AC_USR_NICK
                                fetch first <<first_rows>> rows only)
    PAGINATE_TABLE_TMPNAME )
where PAGINATE_COL_ROWNBR between ? and ?;