环境:Mysql+Mybatis
需求:每个省份单独有一张表,每个表统计“待提交”、“待审核"、"已通过"和”总条数“信息。一条sql解决,不用传统方式循环查询上百次,从而提高查询效率和减轻数据库压力。
dao-impl:
@Override
public List<Map<String, Object>> getCountByBathSql(List<Users> userList) {
if(userList != null && userList.size() > 0) {
return getReadSqlSession().selectList("suidaoxinxicaijiDao.getCountByBathSql",userList);
}else {
return null;
}
}
参数 userList:sql需要使用List中每个Users对象的id和省份名称provinceName,其中Users对象为省厅用户,id为每个省份分表的标识符,用于确定表名,provinceName在统计结果中返回,用于前台显示
mybatis:
<!-- 批量查询符合条件的数量 -->
<select id="getCountByBathSql" parameterType="java.util.List" resultType="map">
select t.* from
<foreach collection="list" index="index" item="user" open="(" separator="UNION" close=")">
select
sum(case when zhuangtai = 0 then 1 else 0 end) count1 ,<!-- 待提交 -->
sum(case when zhuangtai in (1,2,3,4) then 1 else 0 end) count2 ,<!-- 待审核 -->
sum(case when zhuangtai = 5 then 1 else 0 end) count3,<!-- 通过 -->
count(*) count4,
#{user.provinceName} provinceName, <!-- 返回当前数据所属省份,前台显示 -->
#{user.id} userId <!-- 用户分表的标识符 -->
from suidaoxinxicaiji_#{user.id}
</foreach> t
</select>
简化的案例sql:
select t.* from (
select
sum(case when zhuangtai = 0 then 1 else 0 end) count1 ,
sum(case when zhuangtai in (1,2,3,4) then 1 else 0 end) count2 ,
sum(case when zhuangtai = 5 then 1 else 0 end) count3,
count(*) count4
from suidaoxinxicaiji_789
UNION
select
sum(case when zhuangtai = 0 then 1 else 0 end) count1 ,
sum(case when zhuangtai in (1,2,3,4) then 1 else 0 end) count2 ,
sum(case when zhuangtai = 5 then 1 else 0 end) count3,
count(*) count4
from suidaoxinxicaiji_828
) t
查询结果: