通过生日查询各年龄段数量通过饼状图显示

数据库表t_member中 birthday 字段为date类型

 

需求:根据年龄段（可以指定几个年龄段，例如0-18、18-30、30-45、45以上）展示各个年龄段的占比，通过饼形图来展示(Echarts)

 

实现:最重要的就是sql语句:

<select id="findMemberAgeCount" resultType="map">
    SELECT (CASE
    WHEN a.age IS NULL THEN 'unknown'
    WHEN a.age &lt; 18 THEN '0-18岁'  #then后面的为前端显示数据,也就是Echarts显示数据
    WHEN a.age BETWEEN 18 AND 30 THEN '18-30岁'
    WHEN a.age BETWEEN 31 AND 45 THEN '30-45岁'
    WHEN a.age > 45 THEN '45岁以上'
    END) as name,
    COUNT(*) 'value'  #name和value需要这样写,和Controller层数据对应
    FROM (SELECT NOW() AS "时间",birthday,TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age FROM t_member  )a
    GROUP BY name
</select>

---

Controller层,service,dao以及dao.xml中的方法都是:findMemberAgeCount  

Controller层如下()其它层非常简单,省略)

@RequestMapping("/getMemberAgeReport")
public Result getMemberAgeReport(){
    try {
        List<Map<String,Object>> list = memberService.findMemberAgeCount();
        Map<String,Object> map = new HashMap<>();
        map.put("ageCount",list);

        List<String> memberAge = new ArrayList<>();

        for (Map<String, Object> maplist : list) {
            String name = (String) maplist.get("name");
            memberAge.add(name);

        }

        map.put("memberAge",memberAge);
        return new Result(true,MessageConstant.GET_MEMBERAGE_COUNT_REPORT_SUCCESS,map);
    }catch (Exception e){
        e.printStackTrace();
        return new Result(false,MessageConstant.GET_MEMBERAGE_COUNT_REPORT_FAIL);
    }
}

---

注意细节:前端使用vue:
　　ajax请求:  axios.get("/report/getMemberAgeReport.do")
　　
　　封装是数据:通过分析得出需要封装成 List<Map<String,Object>>

legend: {
    orient: 'vertical',
    left: 'left',
    data: res.data.data.memberAge
},
series : [
    {
        name: '会员年龄占比',
        type: 'pie',
        radius : '55%',
        center: ['50%', '60%'],
        data:res.data.data.ageCount,
        itemStyle: {
            emphasis: {
                shadowBlur: 10,
                shadowOffsetX: 0,
                shadowColor: 'rgba(0, 0, 0, 0.5)'
            }
        }

---