springboot——统计

人员统计

做一个公司的统计显示部门和人数

<select id="getScoreStatistics" resultType="java.util.Map">
    SELECT
    xwe.DeptName     as deptname,
    COUNT(*)         as number
    FROM xinjing_worker_examine  xwe
    LEFT JOIN traindeptemp       temp   ON xwe.MemberId = temp.MemberId
    LEFT JOIN traindeptinfo      tin    ON tin.DeptCode = xwe.DeptId

    <if test="examStartTm3 != null and examStartTm3 !=''">
      and day(xwe.exam_start_tm) = #{examStartTm3}
    </if>

    <if test="examStatus != null and examStatus !=''">
      and exam_status = #{examStatus}
    </if>

    <if test="deptName != null and deptName !=''">
      and DeptName = #{deptName}
    </if>

    <if test="groupName != null and groupName !=''">
      and groupName = #{groupName}
    </if>

    <if test="studentScore != null and studentScore !='' and studentScore2 != null and studentScore2 !='' ">
      and student_score between #{studentScore} and #{studentScore2}
    </if>
    GROUP BY xwe.DeptName
  </select>

Dao层接口

/**
     * 成绩统计(时间为具体日)
     * @param trainStuexamResultVo
     * @return
     */
    List<Map<String,Object>> getScoreStatistics(TrainStuexamResultVo trainStuexamResultVo);

server层

/**
     * 成绩统计(具体到日)
     * @param trainStuexamResultVo
     * @return
     */
    List<Map<String,Object>> getScoreStatistics(TrainStuexamResultVo trainStuexamResultVo);

实现类

/**
     * 成绩统计（具体到 日）
     * @param trainStuexamResultVo
     * @return
     */
    @Override
    public List<Map<String, Object>> getScoreStatistics(TrainStuexamResultVo trainStuexamResultVo) {
        return traindeptinfoDao.getScoreStatistics(trainStuexamResultVo);
    }

Controller层

@Autowired
    private TrainDeptinfoService trainDeptinfoService;

    /**
     * 成绩统计(具体到日)
     * @param trainStuexamResultVo
     * @return
     */
    @RequestMapping(value="getScoreStatistics",method= RequestMethod.POST,produces = "application/json;charset=UTF-8")
    public JsonResult getScoreStatistics(@RequestBody TrainStuexamResultVo trainStuexamResultVo){
        int pageSize = trainStuexamResultVo.getPageSize();
        int pageNum = trainStuexamResultVo.getPageNum();
        if (pageSize > 50) {
            pageSize = 50;
        }
        PageHelper.startPage(pageNum,pageSize);
        List<Map<String,Object>> list = trainDeptinfoService.getScoreStatistics(trainStuexamResultVo);
        for (Map<String,Object> map : list){
            //部门名称
            map.get("deptname");
            //人数
            map.get("number");
        }
        PageInfo<Map<String,Object>> pageTrainDeptInfo = new PageInfo(list);
        return new JsonResult(pageTrainDeptInfo);
    }

总结

想要把sql语句里面的函数也返回出来 ：
1.SQL语句给返回参数起别名
2.接受参数用List<Map<String,Object>>
3.map.get(“别名”) 就ok了