MYSQL常用工具

1、字符串截取

substring(INDEX_NAME, 3, 2) -----------INDEX_NAME

2、String 转 Int

CAST(INDEX_NAME AS SIGNED integer) ------------INDEX_NAME

3、时间格式化

date_format(INDEX_NAME, ‘%Y-%m-%d %H:%i:%s’) ----------------INDEX_NAME

4、IFNULL()、CASE-WHEN 组合使用

ifnull(
CASE
WHEN pol_code = ‘01’ THEN ‘中国共产党’
WHEN pol_code = ‘13’ THEN ‘群众’
WHEN pol_code = ‘14’ THEN ‘共青团员’
END, ‘-’) pol_code,

5、一对多

@Data
public class TreeDTO {

private String key;
private String value;
private String flag;
private List<TreeDTO> children;

}

<!-- 省市县 -->
<resultMap id="treeDTO" type="com.hzsun.zbp.inno.platform.model.dto.part2.TreeDTO"><id column="key" jdbcType="VARCHAR" property="key"></id><result column="value" jdbcType="VARCHAR" property="value"></result><result column="flag" jdbcType="VARCHAR" property="flag"></result><collection property="children" ofType="com.hzsun.zbp.inno.platform.model.dto.part2.TreeDTO" notNullColumn="city_key"><id column="city_key" jdbcType="VARCHAR" property="key"></id><result column="city_value" jdbcType="VARCHAR" property="value"></result><result column="city_flag" jdbcType="VARCHAR" property="flag"></result><collection property="children" ofType="com.hzsun.zbp.inno.platform.model.dto.part2.TreeDTO" notNullColumn="county_key"><id column="county_key" jdbcType="VARCHAR" property="key"></id><result column="county_value" jdbcType="VARCHAR" property="value"></result><result column="county_flag" jdbcType="VARCHAR" property="value"></result></collection></collection>
</resultMap><!-- 省市县 -->
<select id="listAreas" resultMap="treeDTO">SELECTprivince.code `key`, privince.name `value`,city.code `city_key`, city.name `city_value`,county.code `county_key`, county.name `county_value`FROM tinno_china_area privinceLEFT JOIN tinno_china_area city ON city.pcode = privince.codeLEFT JOIN tinno_china_area county ON county.pcode = city.codeWHERE privince.`level` = 'province'
</select>

6、根据指定顺序排序

ORDER BY a.top_flag DESC, field(a.status, ‘1’, ‘2’, ‘0’, ‘3’, ‘4’, ‘5’, ‘6’) ASC, edit_time DESC

7、获取最近四年（>9月则从当年算起，否则从去年算起）

select concat(date_format(date_sub(curdate(), interval + if(MONTH(NOW())>9, 3, 4) YEAR), ‘%Y’))
union all
select concat(date_format(date_sub(curdate(), interval + if(MONTH(NOW())>9, 2, 3) YEAR), ‘%Y’))
union all
select concat(date_format(date_sub(curdate(), interval + if(MONTH(NOW())>9, 1, 2) YEAR), ‘%Y’))
union all
select concat(date_format(date_sub(curdate(), interval + if(MONTH(NOW())>9, 0, 1) YEAR), ‘%Y’))