mysql 执行时间饼图_通过生日查询各年龄段数量通过饼状图显示

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

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

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

SELECT (CASE

WHEN a.age IS NULL THEN 'unknown'

WHEN a.age < 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

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

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

@RequestMapping("/getMemberAgeReport")

public Result getMemberAgeReport(){

try {

List> list = memberService.findMemberAgeCount();

Map map = new HashMap<>();

map.put("ageCount",list);

List memberAge = new ArrayList<>();

for (Map 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>

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)'

}

}