MYSQL采用union all 合并去重，相同值累加

SELECT pe.person_type AS personType, '0' AS plaitAmount, '0' AS id, COUNT(pe.id) AS strengthAmount
FROM t_person pe
WHERE pe.deleted = 0
    AND pe.department_id = 5
GROUP BY pe.person_type
UNION ALL
SELECT bz.person_type AS personType, bz.plait_amount AS plaitAmount, bz.id AS id, 0 AS strengthAmount
FROM t_preparation_rybzsbz bz
WHERE bz.deleted = 0
    AND bz.department_id = 5

查询结果如下：

采用Java算法，合并相同的personType,并且各字段结果累加，如下：

    /**
     * @author wangbin
     * @date 2019-7-25  17:39
     * @Description: 将查询的结果按照persontype合并
     *@Version 1.0
    */
    private List<PreparationRybzsbzVO> buildPersonType(List<PreparationRybzsbzVO> preparationRybzsbzList){
        Map<Integer,PreparationRybzsbzVO> typeMap = new HashMap<>();
        for (PreparationRybzsbzVO item : preparationRybzsbzList) {
            if(typeMap.containsKey(item.getPersonType())){
                PreparationRybzsbzVO p = typeMap.get(item.getPersonType());
                p.setPlaitAmount(p.getPlaitAmount()+item.getPlaitAmount());
                p.setStrengthAmount(p.getStrengthAmount()+item.getStrengthAmount());
                p.setId(p.getId()+item.getId());
                typeMap.put(item.getPersonType(),p);
            }else{
                typeMap.put(item.getPersonType(),item);
            }
        }
        List<PreparationRybzsbzVO> preparationRybzsbzVOList = new ArrayList<>();
        for(Map.Entry<Integer,PreparationRybzsbzVO> entry:typeMap.entrySet()){
            preparationRybzsbzVOList.add(entry.getValue());
        }
        return preparationRybzsbzVOList;
    }

最终结果集如下：

 

转载于:https://www.cnblogs.com/it-way/p/11246150.html