1、union all 的使用
<select id="getCountByTime" resultType="java.lang.Integer">
select count(0) as count
from sys_user
where create_time >= #{countTimeS}
and create_time <= #{countTimeE}
union all
select count(0) as count
from sys_role
where create_time >= #{countTimeS}
and create_time <= #{countTimeE}
union all
select count(0) as count
from sys_menu
where create_time >= #{countTimeS}
and create_time <= #{countTimeE}
</select>
<!--
List<Integer> getCountByTime(@Param("countTimeS") Date countTimeS,
@Param("countTimeE") Date countTimeE);
list.get(0) 用户数
list.get(1) 角色数
list.get(2) 菜单数
-->
2、case的使用
select
user_name,case sex when 0 then '男' when 1 then '女' else '未知' end as sex,role_name
from sys_user u,sys_user_role ur,sys_role r
where u.user_id = ur.user_id and ur.role_id = r.role_id
-- case 列名
-- when 属性值 then 转换值
-- when 属性值 then 转换值
-- when 属性值 then 转换值
-- else 转换值
-- end as 新列名