【sql】不同库查询前几条记录用法

SQL在不同数据库中查询前几条记录的用法分类

方法

	/**
     * 查询前几条记录SQL
     * <p>
     * 1. ORACLE
     * SELECT * FROM TABLE1 WHERE ROWNUM <= N
     * 4. SQL SERVER
     * SELECT TOP N * FROM TABLE1
     * 6. MYSQL
     * SELECT * FROM TABLE1 LIMIT N
     * 8.POSTGRES
     * SELECT * FROM TABLE LIMIT  N
     *
     * @param selectSql
     * @param dbType
     * @return
     */
    public static String selectTopNum(String selectSql, String dbType) {
        int limitNum = 1000;
        if (StringUtils.isBlank(dbType)) {
            dbType = DbTypeEnum.MYSQL.getCode();
        }
        String sqlTemp = "";
        switch (DbTypeEnum.getEnumByCode(dbType)) {
            case POSTGRESQL:
            case MYSQL:
                //WITH TEMP_A AS (select * from TABLE1) SELECT * FROM TEMP_A LIMIT 10
                sqlTemp = "WITH TEMP_A AS ({0}) SELECT * FROM TEMP_A LIMIT {1}";
                break;
            case DM:
            case OSCAR:
            case KINGBASE86:
            case KINGBASE8:
            case ORACLE:
                //WITH TEMP_A AS (SELECT * FROM TABLE1) SELECT * FROM TEMP_A WHERE ROWNUM <=  100
                sqlTemp = "WITH TEMP_A AS ({0}) SELECT * FROM TEMP_A WHERE ROWNUM <= {1}";
                break;
            case SQLSERVER:
                //WITH TEMP_A AS (SELECT * FROM TABLE1) SELECT TOP 100 * FROM TEMP_A
                sqlTemp = "WITH TEMP_A AS ({0}) SELECT TOP {1} * FROM TEMP_A";
                break;
            default:
        }
        selectSql = MessageFormat.format(sqlTemp, selectSql, limitNum);
        return selectSql;
    }

附加

不同数据库中查询前几条记录的用法