Service的方法定义:
BusinessSizeVo businessSizeInfo(@Param("dto") ResourceOverviewParamDto dto);
mybatis配置
<resultMap id="BusinessSizeVo" type="com.iwhalecloud.ids.busi.analyse.resourceOverview.controller.vo.BusinessSizeVo">
<result column="areaid" property="areaId"/>
<result column="areaname" property="areaName"/>
<result column="jkyhs" property="homeBroadbandSum"/>
<result column="dsyhs" property="tvClientSum"/>
<result column="ghyhs" property="telClientSum"/>
<result column="kjyhs" property="lookHomeClientSum"/>
<result column="znzwyhs" property="intelligentNetworkClientSum"/>
<result column="ajtvyhs" property="loveHomeTvClientSum"/>
<result column="qkyhs" property="businessBroadbandSum"/>
<result column="cyyqdzs" property="industrialParkAddressSum"/>
<result column="jdbgdzs" property="hotelAddressSum"/>
<result column="swlydzs" property="businessBuildingAddressSum"/>
<result column="zyscdzs" property="professionalMarketAddressSum"/>
<result column="spdpdzs" property="shopAddressSum"/>
</resultMap>
<!--业务规模-查某地市总数据-->
<select id="businessSizeInfo"
parameterType="com.iwhalecloud.ids.busi.analyse.resourceOverview.controller.dto.ResourceOverviewParamDto"
resultMap="BusinessSizeVo" >
SELECT citycode as areaid,cityname as areaname, ywgm.* FROM ids_analyse.map_ywgm_statistic,
(
SELECT
sum(jkyhs) as jkyhs,sum(dsyhs) as dsyhs,sum(ghyhs) as ghyhs,
sum(kjyhs) as kjyhs,sum(znzwyhs) as znzwyhs,sum(ajtvyhs)as ajtvyhs,
sum(qkyhs) as qkyhs,sum(cyyqdzs) as cyyqdzs,sum(jdbgdzs)as jdbgdzs,
sum(swlydzs)as swlydzs,sum(zyscdzs)as zyscdzs,sum(spdpdzs)as spdpdzs
FROM
ids_analyse.map_ywgm_statistic
where citycode = #{dto.areaId,jdbcType=VARCHAR}
)ywgm
where citycode= #{dto.areaId,jdbcType=VARCHAR}
</select>
分数排名,对象传参,属性判空
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.mapper.BWorkerImsPersonInfoDAO">
<resultMap id="QueryScoreVO" type="com.iwhalecloud.gx.yd.brick.worker.score.pojo.vo.QueryScoreVO">
<result column="worker_name" property="workerName"/>
<result column="zq_score" property="zqScore"/>
<result column="sum_score" property="sumScore"/>
<result column="rank" property="rank"/>
</resultMap>
<select id= "queryScoreList" resultMap="QueryScoreVO">
select d.*,dense_rank() over (order by d.zq_score desc) as 'rank' from
(
select
a.worker_name,
c.zq_score,
a.sum_score
from b_worker_ims_person_info a,
(select b.person_info_pk_id,
sum(b.score_change_value) as zq_score
from b_worker_ims_score_history b
where DATE_FORMAT(b.create_date,'%Y-%m')
between #{queryScoreDTO.startMonth} and #{queryScoreDTO.endMonth}
group by b.person_info_pk_id )c
where a.pk_id=c.person_info_pk_id
<if test="queryScoreDTO.district !=null and queryScoreDTO.district !=''">
and a.area_id= #{queryScoreDTO.district}
</if>
)d
limit #{queryScoreDTO.startCount},#{queryScoreDTO.count}
</select>
</mapper>