使用MyBatis对项目中的统计功能进行处理的查询语句优化

（比较重点的我都会以红字标出）前两天刚入职，比较忙所以没有写文章。这篇文章我将会写这两天在一个人才服务业务管理系统项目中碰到的一个关于对MySQL数据库中数据进行统计的小问题。

首先，我这边有两张表，一张是户口表，一张是档案表。

具体需求如下：

从前天传来一个时间范围的参数，按照时间统计各类学历的党员和户口情况数量

然后得到统计结果返回给前台页面

由于学历类别这边的基本是固定不变的，所以我的查询语句也就直接写死，不去动态的获取每种学历的education字段的值了。

由于这边需要考虑横纵两列的条件还要外加一个时间段的条件跟多张表查询，所以整个代码的编写可能会有些复杂，相关的关键代码我也会在下面展示出来给到大家以供参考

首先我在model层写了一个不用映射在数据库的实体类（因为只是统计用的，所以不同对应到数据库的表里），用来保存查询到的数据，以便于传到前台页面

实体类代码

public class Statistics {private String type;// 类型private Long totalStatistic;// 总数private Long doctorStatistic;// 博士private Long masterStatistic;// 硕士private Long regularStatistic;// 本科private Long juniorStatistic;// 专科private Long elseStatistic;// 其他}

当然还需要对应的getter，setter方法，我就不放上来了

接下来是control层的代码

public Object searchByEducationAndAffiliated(HttpServletRequest request, String inTime, String outTime) {if (StringUtils.isBlank(inTime) || inTime.equals("0000/00/00")) {inTime = null;} else {inTime = inTime + " 00:00:00";}if (StringUtils.isBlank(outTime) || outTime.equals("0000/00/00")) {outTime = null;} else {outTime = outTime + " 23:59:59";}Map<String, Object> map = new HashMap<String, Object>();String[] affiliatedStates = new String[] { "党员", "党员挂靠", "党员解除", "户口", "户口挂靠", "户口解除" };String as = new String();List<Statistics> list = new ArrayList<>();for (int i = 0; i < affiliatedStates.length; i++) {as = affiliatedStates[i];map.put("affiliatedStates", as);Statistics statistic = new Statistics();statistic = accountDao.statisticsByEducationAndAffiliated(map);statistic.setType(as);list.add(statistic);}map.put("inTime", inTime);map.put("outTime", outTime);return list;}

首先这边有三个传参

request就不用多解释了，毋庸置疑这是前台传来的一个请求

inTime跟outTime则表示是一个时间段

因为考虑到可能有些用户不输入时间或者输入的时间表示当天的一整天这两种情况，所以在刚开始的时候就通过一个判断给这两个参数进行初始化的赋值。

然后定义了一个Map用来保存传到dao层的方法的参数（由于用了SSM框架，所以原来对数据库进行操作的代码实现就完全放到了Mybatis的xml文件里了），由于要实现的是统计的功能，页面要统计的内容是固定的，所以我直接把页面每行的条件放在了一个数组里，然后用一个for循环，依次传参数给实现方法，通过方法返回一个statistic对象，在把每一个对象放进一个list集合里返回到前台页面获取。
接下来就是比较重要的部分了，也就是statisticsByEducationAndAffiliated()方法具体的实现

首先先给到大家xml部分的代码

<sql id="baseSelectTotal">selectcount(*) as totalStatistic,sum(case when education ='188' then1else 0 end) as doctorStatistic,sum(case when education ='190' then1else 0 end) as masterStatistic,sum(case when education ='194' then1else 0 end) as regularStatistic,sum(case when education ='197'oreducation = '198' oreducation = '199'then1 else 0 end) as juniorStatistic,sum(case wheneducation not in ('188', '190', '194', '197', '198','199')oreducation isnull then1else 0 end) as elseStatisticfrom archives where1=1</sql>

<select id="statisticsByEducationAndAffiliated" parameterType="Map" resultType="com.hd.ams.model.Statistics"><if test="affiliatedStates!=null and affiliatedStates =='党员' or affiliatedStates =='党员挂靠' or affiliatedStates =='党员解除'"><include refid="baseSelectTotal" /><if test="inTime!=null">and create_time>=#{inTime}</if><if test="outTime!=null">and create_time<=#{outTime}</if><if test="affiliatedStates =='党员'">and affiliated is not null</if><if test="affiliatedStates =='党员挂靠'">and affiliated = 'yes'</if><if test="affiliatedStates =='党员解除'">and affiliated = 'no'</if></if><if test="affiliatedStates!=null and affiliatedStates =='户口' or affiliatedStates =='户口挂靠' or affiliatedStates =='户口解除'"><!-- select (select count(*) from account where 1=1 <if test="affiliatedStates =='户口'"> </if> <if test="affiliatedStates =='户口挂靠'"> and state != 'move_out' </if> <if test="affiliatedStates =='户口解除'"> and state = 'move_out' </if> <if test="inTime!=null"> and create_time>=#{inTime} </if> <if test="outTime!=null"> and create_time<=#{outTime} </if> ) as totalStatistic, sum(case when ar.education = '188' then 1 else 0 end) as doctorStatistic, sum(case when ar.education = '190' then 1 else 0 end) as masterStatistic, sum(case when ar.education = '194' then 1 else 0 end) as regularStatistic, sum(case when ar.education = '197' or ar.education = '198' then 1 else 0 end) as juniorStatistic, ((select count(*) from account where 1=1 <if test="inTime!=null"> and create_time>=#{inTime} </if> <if test="outTime!=null"> and create_time<=#{outTime} </if> <if test="affiliatedStates =='户口'"> </if> <if test="affiliatedStates =='户口挂靠'"> and state != 'move_out' </if> <if test="affiliatedStates =='户口解除'"> and state = 'move_out' </if> ) - (select sum(case when ar.education in ('188', '190', '194','197', '198') and ac.card = ar.card then 1 else 0 end) from account ac,archives ar where 1=1 <if test="inTime!=null"> and ac.create_time>=#{inTime} </if> <if test="outTime!=null"> and ac.create_time<=#{outTime} </if> <if test="affiliatedStates =='户口'"> </if> <if test="affiliatedStates =='户口挂靠'"> and ac.state != 'move_out' </if> <if test="affiliatedStates =='户口解除'"> and ac.state = 'move_out' </if> )) as elseStatistic from account ac,archives ar where 1=1 and ar.card = ac.card <if test="inTime!=null"> and ac.create_time>=#{inTime} </if> <if test="outTime!=null"> and ac.create_time<=#{outTime} </if> <if test="affiliatedStates =='户口'"> </if> <if test="affiliatedStates =='户口挂靠'"> and ac.state != 'move_out' </if> <if test="affiliatedStates =='户口解除'"> and ac.state = 'move_out' </if> -->select count(*) as totalStatistic,sum(case when education = '188' then1else 0end) as doctorStatistic,sum(case when education = '190' then1else0 end) as masterStatistic,sum(case when education = '194' then1else 0 end) as regularStatistic,sum(case when education = '197'oreducation = '198' oreducation = '199'then1 else 0 end) as juniorStatistic,sum(case wheneducation not in ('188', '190', '194','197','198','199')or education is nullthen1 else 0 end)as elseStatisticfrom (select ac.create_time,ac.state,ar.educationfrom account ac left join archives aron ac.card = ar.card group by ac.card) statistics where 1=1<if test="inTime!=null">and create_time>=#{inTime}</if><if test="outTime!=null">and create_time<=#{outTime}</if><if test="affiliatedStates =='户口'"></if><if test="affiliatedStates =='户口挂靠'">and state != 'move_out'</if><if test="affiliatedStates =='户口解除'">and state = 'move_out'</if></if></select>

看到这里，有些小伙伴可能就有点懵逼了，不要慌，我接下来带大家来一一解读每个语句的具体意思。

首先在此之前告知大家一个小技巧，如果在xml文件需要多次引用重复的sql语句，可以通过一个<sql>标签将重复的sql语句放入其中，然后在要用到的地方用<include>标签引用（这跟JSP页面引用别的页面有些类似，如果引用的不是当前文件的则refid需要全路径）

这边xml是通过<mapper>标签可以对应到dao层里具体的某个类 然后通过<select>标签里的每个id属性知道对应的是类里的哪个具体的方法，paramType和resultType则是声明传参的类型和返回的结果类型

接下来我则使用sum函数再加上case when条件来统计不同学历的学生的数量（这边还不用加上其他查询条件），其中的then 1 else 0的意思是如果满足when的条件则计数，若不满足则不计数，由于党员状态这边的查询只涉及到一张表的统计，所以会比较简单,可以通过多个<if>标签判断传参来确定要执行哪些sql语句直接解决问题。这边可以参照这位前辈的博客文章https://www.cnblogs.com/zcy_soft/archive/2011/03/10/1979357.html

当统计户口情况的时候，就比较棘手了，涉及到户口表和档案表两张表，刚开始我用的是注释里的sql语句，相当的麻烦，因为是通过card字段进行的表关联，card字段不是唯一的，而且可能为空，所以分学历种类查询中可能就需要分三种情况来处理，总数的查询为一类（直接统计account表里的所有数据记录量），博士、硕士、本科、专科的为一类（按照archives表里的education字段分类统计），其他的为一类（因为其他要包括education没有的情况跟card字段的值为空的情况），总之是比较麻烦的一种选择，不信的小伙伴可以自己试试，查询统计速度相当慢，我甚至还怀疑是自己网络的问题。

第二种方法是我后来进行sql优化后的方法，直接使用了一个多表的左连接的子查询作为from的条件，其中还用group by对card相同的情况进行了去重，而且也不怕card为null的情况了，因为直接是对连接后得到的虚拟表进行的查询，所以为null还是能够查询到并进行计数的，这边具体的话可以参照这位前辈的博客文章http://blog.csdn.net/Jintao_Ma/article/details/51260458

这边有个小点需要跟大家提一下就是当我写where加条件的时候都加了个1=1是什么意思，因为这边的条件都是要进行判断才会选择执行哪一句的，每一个条件前面都有一个and，因为where后面的第一个条件是不能加and的，所以我如果不加1=1的话，判断出来是第一个条件则sql语句将会是where and 。。。，这sql语句显然是有错的，所以1=1的作用就是防止出错

好了 本篇文章就写到这里了。有兴趣一起讨论技术或者 交流的小伙伴可以私信我，周一到周五可能工作会没时间回复大家，但我看到的话一定会回复的