【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;
    }

附加

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