获取最小可用编码

Mysql

    /**
     * 获取一个新的科目编码
     *
     * @param parentCode 上级科目代码
     * @author 吕嘉鸣
     */
    @Override
    public String getNewCode(String subjectTableName, Long deptId, String parentCode) {
        String oldCode = parentCode;
        parentCode = parentCode + "___";
        String newCode = sysSubjectMapper.getNewCode(subjectTableName, deptId, parentCode);
        if (null == newCode || "".equals(newCode)) {
            return oldCode + "001";
        } else {
            String tailCode = newCode.substring(newCode.length() - 3, newCode.length());

            newCode = oldCode + String.format("%03d", Integer.valueOf(tailCode) + 1);
        }
        return newCode;
    }

    /**
     * 根据当前科目编码获取最小可用值
     *
     * @param tableName 科目表名
     * @param deptId 机构id
     * @param parentCode 上级科目代码
     * @return 未使用的最小的科目编码
     * @author 吕嘉鸣(idea)
     */

    @Select(" SELECT code AS newCode " +
            " FROM ${tableName} AS t1 WHERE  t1.`code` LIKE #{parentCode}  AND  right(code, 3) != '999' AND t1.deleted = 0" +
            " AND not exists(select code  FROM ${tableName} AS t2 WHERE t2.dept_id = #{deptId} AND t2.deleted = 0 AND  CONVERT(t2.code,SIGNED)=CONVERT(t1.code,SIGNED)+1  ) order by CONVERT(right(code, 3),SIGNED) limit 1;")
    String getNewCode(String tableName,Long deptId,String parentCode);

PostgreSql

/**
     * 根据当前编码获取最小可用编码
     * @param parentCode 父code
     * @return 最小可用code
     */
    private String getNewCode(String parentCode,Integer type) {
        String newCode = null;
        try {
            if (StringUtils.isEmpty(parentCode) || "null".equals(parentCode)){
                parentCode = "";
            }
            String oldCode = parentCode;
            parentCode = parentCode + "___";
            newCode = resourceTreeMapper.getNewCode(parentCode,type);
            if (null == newCode || "".equals(newCode) || "null".equals(newCode)) {
                return oldCode + "001";
            } else {
                String tailCode = newCode.substring(newCode.length() - 3, newCode.length());

                newCode = oldCode + String.format("%03d", Integer.valueOf(tailCode) + 1);
            }
        }catch (Exception ex){ex.printStackTrace();}
        return newCode;
    }

	/**
     * 获取最小可用编码
     * @param parentCode 上级科目代码
     * @return 未使用的最小的科目编码
     */
	<select id="getNewCode" parameterType="java.lang.String"
            resultType="java.lang.String">
            SELECT
                code AS newCode
            FROM t_resoure_tree  AS t1
                WHERE  t1.code LIKE #{parentCode}  AND  right(code, 3) != '999'
            AND
                not exists(
                    select
                        code
                    FROM t_resoure_tree AS t2
                        WHERE cast(t2.code as DECIMAL) = cast(t1.code as DECIMAL)+1 ) order by cast(right(code, 3) as DECIMAL) limit 1;
    </select>