这个需求我是实现了,但是在代码的优化上没有做到位,其实可以写的更加简洁,但是因为时间紧,就没有想那么多。下面附上Java接口逻辑代码和mybatis的SQL代码:
/**
* 使用身份证判断年龄段
* @param addressid
* @return
*/
@RequestMapping("/toAge")
@ResponseBody
public Object toAge(String id,String type){
Map<String, Object> map=new HashMap<String, Object>();
Subject currentUser = SecurityUtils.getSubject();
String userid = currentUser.getSession().getAttribute("_USER_ID").toString();
List<Community> age=dataAnalyzeService.selectAddressAge(id,type,userid);
//初始化参数
int c1=0;int c2=0;int c3=0;
int c4=0;int c5=0;int c6=0;
for(int i=0;i<age.size();i++){
String group=age.get(i).getAge_group();//循环得出年龄段对比后传值
Integer count=age.get(i).getAge_count();
if(group.equals("0~18")){
c1=count;
}
if(group.equals("19~30")){
c2=count;
}
if(group.equals("31~40")){
c3=count;
}
if(group.equals("41~50")){
c4=count;
}
if(group.equals("51~60")){
c5=count;
}
if(group.equals("60以上")){
c6=count;
}
}
map.put("c1", c1);//以此存入map中
map.put("c2", c2);
map.put("c3", c3);
map.put("c4", c4);
map.put("c5", c5);
map.put("c6", c6);
return map;
}
<!--使用身份证判断年龄段 -->
<select id="selectAddressAge" resultType="com.oracle.pojo.Community" parameterType="java.lang.String">
select
case
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 0
and months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 < 19
then '0~18'
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 19
and months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 < 31
then '19~30'
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 31
and months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 < 41
then '31~40'
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 41
and months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 < 51
then '41~50'
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 51
and months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 < 60
then '51~60'
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 60
then '60以上'
end age_group, count(1) AS age_count from cmu_userdevice e
LEFT JOIN cmu_subscriber a ON a.COMMUNITYKEY =e.COMMUNITYKEY
LEFT JOIN CMU_CUSTOMER t ON t.SUBSCRIBERKEY = a.SUBSCRIBERKEY
LEFT JOIN CMU_COMMUNITY c ON c.COMMUNITYKEY = a.COMMUNITYKEY
RIGHT OUTER JOIN
(select * from CMU_USERS start with USER_ID = #{userid,jdbcType=VARCHAR} connect by prior USER_ID = PID) f ON e.USER_ID = f.USER_ID
WHERE
<if test="type == 1">
c.PROVINCEID = #{id, jdbcType=VARCHAR}
</if>
<if test="type == 2">
c.cityid = #{id, jdbcType=VARCHAR}
</if>
<if test="type == 3">
c.areaid = #{id, jdbcType=VARCHAR}
</if>
<if test="type == 4">
c.ADDRESSID = #{id, jdbcType=VARCHAR}
</if>
AND t.identity is NOT NULL
group by
case
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 0
and months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 < 19
then '0~18'
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 19
and months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 < 31
then '19~30'
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 31
and months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 < 41
then '31~40'
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 41
and months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 < 51
then '41~50'
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 51
and months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 < 60
then '51~60'
when months_between(sysdate,to_date(substr(t.identity,7,8),'yyyymmdd'))/12 >= 60
then '60以上'
end
ORDER BY instr('0~18 19~30 31~40 41~50 51~60 60以上',NULL)
</select>
详解:在接口中,我直接简单的使用的if进行区分,取出值以后就直接通过Map返回给了前端,前端使用ECharts图表对数据进行展示,因为前后端都是我一个人编写的,所以优化方面做的不太到位!请不要喷我!
SQL解释:首先SQL的if块你们不需要去看,这是我自己需求需要的 还有相关的表结构必须写的关联表语句,大家只需要了解sql语句的头部和尾部,t.identity是身份证的列,你需要更换为自己的列,还有年龄范围的更改,也可以改为你自己划分的范围,但是group up 需要与其同步更改!
前端效果图: