Java使用代码动态列转行

1.需求

 2.数据库表

 

 

 3.代码

public R getInfo() {

        List<Map> maps = new ArrayList<>();

        //每个用户总项目平均值
        List<ReDto> lists = recordDao.getAllAvg();

        for (ReDto reDto : lists) {
            Map<String, Object> map = new HashMap<>();
            //每个用户的各项目平均值
            List<SurveyDto> v = recordDao.getoneAvg(reDto.getName());
            map.put("name", reDto.getName());
            map.put("avg", reDto.getAvg());
            map.put("project", v);
            maps.add(map);
        }

        return R.ok(maps);
    }

@Data
public class ReDto {
    
    /**
     *项目名
     */
    private String name;
    
    /**
     *平均值分数
     */
    private BigDecimal avg;
}

 

//每个用户总项目平均值
List<ReDto> lists = recordDao.getAllAvg();

getAllAvg()方法如下:

-- 每个人的总平均值
	SELECT a.name,avg(a.score)as avg
	from
	(
	select aa.code,aa.user,aa.score,bb.name as project,cc.name
	from
	(SELECT code,user,time,score FROM record) as aa
	LEFT join
	(SELECT code,name FROM survey) as bb
	on aa.code = bb.code
	LEFT join
	(SELECT code,name FROM user) as cc
	on aa.user = cc.code
	) a
	GROUP BY name

@Data
public class SurveyDto {
    //分数
    private BigDecimal score;
    //项目
    private String project;

}

List<SurveyDto> v = recordDao.getoneAvg(reDto.getName());

getoneAvg(name)方法如下

select avg(a.score)as score ,a.project
from (
select aa.code,aa.user,aa.score,bb.name as project,cc.name
	from
	(SELECT code,user,time,score FROM record) as aa
	LEFT join
	(SELECT code,name FROM survey) as bb
	on aa.code = bb.code
	LEFT join
	(SELECT code,name FROM user) as cc
	on aa.user = cc.code
	) a
	where a.name = #{name}
	GROUP BY a.project

3.返回结果

{
    "code": 0,
    "msg": null,
    "data": [
        {
            "avg": 45.3333,
            "name": "张三",
            "project": [
                {
                    "score": 40.0000,
                    "project": "调查项一"
                },
                {
                    "score": 22.0000,
                    "project": "调查项三"
                },
                {
                    "score": 90.0000,
                    "project": "调查项二"
                }
            ]
        },
        {
            "avg": 45.2500,
            "name": "李四",
            "project": [
                {
                    "score": 35.0000,
                    "project": "4"
                },
                {
                    "score": 32.0000,
                    "project": "调查项三"
                },
                {
                    "score": 82.0000,
                    "project": "调查项二"
                }
            ]
        }
    ]
}

数据库结果

 以上代码是个人瞎琢磨出来的,sql能力有限,只能通过代码处理,有更好的办法还往请教!