业务场景
Mysql查询数据后，同时需要根据其中某一个字段值进行排名处理，简单sql如图
SELECT id,user_id,sales_performance,(@i:=@i+1) rank from crm_account_user_performance_data,(SELECT @i:=0) t WHERE dept_id=307 ORDER BY sales_performance DESC;
其中： (@i:=@i+1)代表定义一个变量，每次增加1，整体业务就是查询表数据同时根据sales_performance倒序后赋予排名。
java业务代码
先根据整表查询去重的dept_id，再在各dept_id下查询数据的sales_performance倒序获得排名信息，后批量更新到数据库rank排名字段保存数据
List<Long> deptlist = accountUserPerformanceDataMapper.selectDeptIdsByAccountTime(date); if (CollectionUtils.isNotEmpty(deptlist)) { //遍历为每个部门下人员进行业绩排序 for (Long deptId : deptlist) { List<AccountUserPerformanceData> list = accountUserPerformanceDataMapper.selectRankByDeptId(deptId); //批量更新本部门排序 accountUserPerformanceDataMapper.updateRankBatch(list); } }
xml代码,获取dept_id集合
<select id="selectDeptIdsByAccountTime" parameterType="Date" resultType="java.lang.Long"> SELECT DISTINCT dept_id FROM crm_account_user_performance_data WHERE account_time = #{accountTime} </select>
获取各dept_id内部根据sales_performance倒序排列的序号值
<select id="selectRankByDeptId" parameterType="Long" resultMap="AccountUserPerformanceDataResult"> SELECT id,user_id,(@i:=@i+1) rank from crm_account_user_performance_data,(SELECT @i:=0) t WHERE dept_id=#{deptId} ORDER BY sales_performance DESC </select>
批量更新到数据表中
<update id="updateRankBatch" parameterType="List"> update crm_account_user_performance_data set rank = case id <foreach collection="list" item="account" separator=" "> when #{account.id} then #{account.rank} </foreach> end where id in <foreach collection="list" item="account" open="(" separator="," close=")"> #{account.id} </foreach> </update>
注：本文设计Mysql获取数据排序序号及批量更新数据库相关操作，日常工作记录，需要的博友自行参考哈。
