两次分组计算员工工资的SQL(MyBatis框架)

需求介绍

字段	注释
id	自增ID
hrID	区分管理员
name	姓名
nameID	职员代码
userinfoID	无关紧要
time	值班时间
templateID	当前需求无关紧要的字段
shifts	班次
hierarchy	津贴
coefficient	系数
isEnable	当前需求无关紧要的字段
remarkSUM	汇总备注
remark	普通备注
updater	修改者
updaterTime	修改时间
updaterIP	修改者IP

要求按照班次(shifts)和职员代码(nameID)分组求系数乘以津贴（coefficient *hierarchy）的积，然后再按照nameID分组求系数乘以津贴（coefficient *hierarchy）的积的和；

解释：把班次相同并且职员代码相同的人的系数乘上津贴的积，然后在上一步的基础上把职员代码相同的计算后的值再次分组

Mapper.xml

<select id="listTimeWorkSUM" resultMap="BaseResultMap" parameterType="java.util.List"> 
	    SELECT NAME,nameID,remarkSUM,updater, updaterTime,updaterIP,SUM(hierarchy) hierarchy FROM 
		(SELECT NAME,nameID,shifts,remarkSUM,updater, updaterTime,updaterIP,
		SUM(coefficient) coefficient,SUM(coefficient)*hierarchy hierarchy
		FROM WORK WHERE nameID IN
		<foreach collection="list" item="item" open="(" separator="," close=")">
		    #{item.nameID}
	    </foreach>
		AND YEAR(TIME)=#{year} AND MONTH(TIME)=#{month} AND hrID=#{hrID}
		GROUP BY nameID,shifts ORDER BY nameID) nieqiang GROUP BY nameID
</select>

<!-- 去重查询nameID -->
<select id="listTimeWorkNameID" resultMap="BaseResultMap">
		SELECT DISTINCT(nameID), name FROM WORK
</select>

Mapper.java

List<Work> listTimeWorkSUM(Integer hrID, int year, int month, @Param("list")List<Work> list);
List<Work> listTimeWorkNameID();

Service.java

/**
*	UserIPUtil.getCurrentHrId()工具类
*	public class UserIPUtil {
*		public static Integer getCurrentHrId() {
*			return ((Hr)SecurityContextHolder.getContext().getAuthentication().getPrincipal()).getId();
*		}
*	}
**/
public List<Work> listTimeWorkSUM(Integer hrID, Integer year, Integer month) {
		if(listTimeWorkNameID().size() == 0) {
			return null;
		}
		if (UserIPUtil.getCurrentHrId() == 1) {
			List<Work> work = workMapper.listTimeWorkSUM(hrID, year, month, listTimeWorkNameID());
			//Map<String, Double> map = work.stream().collect(Collectors.groupingBy(Work::getNameID,Collectors.summingDouble(Work::getHierarchy)));
			return work;
		}
	    List<Work> work = workMapper.listTimeWorkSUM(UserIPUtil.getCurrentHrId(), year, month, listTimeWorkNameID());
		return work;
	}
	
public List<Work> listTimeWorkNameID(){
	return workMapper.listTimeWorkNameID();
}