MYSQL,Oracle,DB2,SQLServer4中数据库的分页写法

MySQL:

SELECT
          CHANNEL_ID,
          OPERATOR,
          ORGANIZATION_ID,
          BIZ_TYPE_CODE,
          SEAL_ID,
          count(DISTINCT OPERATION_LOG_ID) count
FROM
          SEAL_LOG
WHERE
           SEAL_ID != ''
GROUP BY
           CHANNEL_ID,
           OPERATOR,
           ORGANIZATION_ID,
           BIZ_TYPE_CODE,
           SEAL_ID
LIMIT 0,100

注:只需要用limit 开始编号，取的数量

Oracle:

SELECT
          *
FROM
 (
          SELECT
                     A .*, ROWNUM AS r
           FROM
                     (
                          SELECT
                                     CHANNEL_ID,
                                      OPERATOR,
                                     ORGANIZATION_ID,
                                     BIZ_TYPE_CODE,
                                     SEAL_ID,
                                     COUNT (DISTINCT OPERATION_LOG_ID) COUNT
                              FROM
                                       SEAL_LOG
                             WHERE
                                         SEAL_ID IS NOT NULL
                            GROUP BY
                                           CHANNEL_ID,
                                             OPERATOR,
                                            ORGANIZATION_ID,
                                            BIZ_TYPE_CODE,
                                             SEAL_ID
                               ) A
                       )
WHERE
 r > 0 AND r <= 100

注：第一对参数的非空判断，oracle中是is not null,其他三种数据库都可以用!=''

       在原有select的基础上做了两层select嵌套，第一层加上编号，第二层筛选，之所以第一层嵌套加编号，是因为group函数分组导致不能在直接查的时候加上编号（和SQL Server,DB2不同）

DB2：

SELECT * FROM(
                SELECT
                         rownumber() over() as rowid,
                        CHANNEL_ID,
                        OPERATOR,
                        ORGANIZATION_ID,
                        BIZ_TYPE_CODE,
                        SEAL_ID,
                        count(DISTINCT OPERATION_LOG_ID) count
                FROM
                        SEAL_LOG b
                WHERE
                        SEAL_ID != ''
                GROUP BY
                        CHANNEL_ID,
                        OPERATOR,
                        ORGANIZATION_ID,
                        BIZ_TYPE_CODE,
                SEAL_ID
        ) a

where rowid>0 and rowid <=2

注：和SQL SERVER一样，第一次查询的时候，就查询出编号，外面套一个SELECT用于筛选个数，不过和SQL SERVER不同的是，这里的OVER()里面可以为空，而SQL SERVER的OVER里面必须加排序字段。

SQL SERVER:

SELECT
           *
FROM
            (
              SELECT
                         row_number () OVER (ORDER BY SEAL_ID) AS rownumber,
                         CHANNEL_ID,
                          OPERATOR,
                         ORGANIZATION_ID,
                         BIZ_TYPE_CODE,
                         SEAL_ID,
                         COUNT (DISTINCT OPERATION_LOG_ID) COUNT
                   FROM
                         SEAL_LOG
                   WHERE
                         SEAL_ID != ''
                   GROUP BY
                         CHANNEL_ID,
                         OPERATOR,
                          ORGANIZATION_ID,
                         BIZ_TYPE_CODE,
                         SEAL_ID
                       ) a
WHERE  a.rownumber BETWEEN 0 AND 100

注：同DB2，不过OVER()函数里面必须要加排序字段，该字段是查询出来的字段，比如此处不能是ID(因为没查出来)

小结：

1:MYSQL,DB2，SQLSERVER的COUNT是一致的，Oracle不一致，区别在于非空判断，Oracle是is not null,别的都是!=’’

2:关于SQL SERVER,DB2的分页都是嵌套一次，第一次查询会查询出编号，但是ORACLE不行，因为Group函数的问题，ORACLE需要嵌套两次。