通过group_concat（）函数来实现
select group_concat(name) from table group by type
select group_concat(name separator ';') from table group by type
select group_concat(name separator ';') from table where column like '%sys%'
当确定列数时可采用下面sql将列转为行
SELECT userid,
SUM(IF(`subject`='语文',score,0)) as '语文',
SUM(IF(`subject`='数学',score,0)) as '数学',
SUM(IF(`subject`='英语',score,0)) as '英语',
SUM(IF(`subject`='政治',score,0)) as '政治'
FROM tb_score
GROUP BY userid
1.mapper.xml文件
<?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.cykj.reports.mapper.main.PerfCsMapper"> <select id="findPercsByPrjAndBuild" resultType="java.util.HashMap">
select
apk_package_name 'apkName',
ROUND(avg(apk_start_time),2) 'stAvg',
ROUND(max(apk_start_time),2) 'stMax',
ROUND(min(apk_start_time),2) 'stMin',
group_concat(test_round) 'roundList',
group_concat(apk_start_time) 'stList'
from
tb_project p inner join tb_build b on p.id = b.prj_id
INNER JOIN tb_test_event e on b.id = e.build_id
inner join tb_report_perf_coldstart pf on e.id = pf.test_event_id
<where>
e.testtype_id = 1
<if test="param1 != null and param1 != ''">
and p.prj_name = #{param1}
</if>
<if test="param2 != null and param2 != ''">
and b.build_name = #{param2}
</if>
</where>
group by apk_package_name
</select></mapper>
2.mapper文件
package com.cykj.reports.mapper.main;import java.util.List;
import java.util.Map;public interface PerfCsMapper {
List<Map<String,String>> findPercsByPrjAndBuild(String prjName, String bdName);
}
3.ServiceImpl.java
package com.cykj.reports.service.impl;import com.cykj.reports.mapper.main.PerfCsMapper;
import com.cykj.reports.service.PerfCsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;
import java.util.Map;@Service
public class PerfCsServiceImpl implements PerfCsService {
@Autowired
private PerfCsMapper perfCsMapper; @Override
public List<Map<String, String>> findPercsByPrjAndBuild(String prjName, String bdName) {
return perfCsMapper.findPercsByPrjAndBuild(prjName,bdName);
}
}
4.Service文件
package com.cykj.reports.service;import java.util.List;
import java.util.Map;public interface PerfCsService {
List<Map<String,String>> findPercsByPrjAndBuild(String prjName, String bdName);
}
5.Controller文件
package com.cykj.reports.controller;import com.cykj.reports.domain.main.*;
import com.cykj.reports.service.*;
import com.cykj.reports.util.ColToRowUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;import java.util.HashMap;
import java.util.List;
import java.util.Map;@Controller
public class PerformanceController {
@Autowired
private PerfCsService perfCsService; //performance页面对应的下拉选所产生的相应数据显示
@RequestMapping(value = "/performance")
public String smoke(Model model){ //7.查询最新projectName和及相关最新buildName对应的perfColdstart信息
List<Map<String,String>> csMaps = perfCsService.findPercsByPrjAndBuild(prjName,bdName);
//重组map集合
List<Map<String, String>> csList = ColToRowUtil.getCsRowList(csMaps); model.addAttribute("csList",csList);
return "performance";
} //project改变时
@RequestMapping(value = "/findPerfDataByProject")
@ResponseBody
public Map<String,Object> findDataByProject(@RequestParam("project") String project){
Map<String,Object> map = new HashMap<>(); //7.查询最新projectName和及相关最新buildName对应的perfColdstart信息
List<Map<String,String>> csMaps = perfCsService.findPercsByPrjAndBuild(project,bdName);
List<Map<String, String>> csList = ColToRowUtil.getCsRowList(csMaps);
map.put("csList",csList); return map;
} //build改变时
@RequestMapping(value = "/findPerfDataByProjectAndBuild")
@ResponseBody
public Map<String,Object> findDataByProjectAndBuild(@RequestParam("project")String project, @RequestParam("build")String build){
Map<String,Object> map = new HashMap<>(); //7.查询最新projectName和及相关最新buildName对应的perfColdstart信息
List<Map<String,String>> csMaps = perfCsService.findPercsByPrjAndBuild(project,build);
List<Map<String, String>> csList = ColToRowUtil.getCsRowList(csMaps);
map.put("csList",csList); return map;
}}
6.ColToRowUtil.java
package com.cykj.reports.util;import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;public class ColToRowUtil { public static List<Map<String,String>> getCsRowList(List<Map<String,String>> maps){
List<Map<String,String>> mapList = new ArrayList<>(); for(Map<String,String> m:maps) {
Map<String, String> map = new HashMap<>();// System.out.println(m.get("bmType"));
map.put("apkName",m.get("apkName"));
map.put("stAvg",String.valueOf(m.get("stAvg")));
map.put("stMax",String.valueOf(m.get("stMax")));
map.put("stMin",String.valueOf(m.get("stMin")));// System.out.println(m.get("roundList"));
String[] roundLists = m.get("roundList").split(",");
// System.out.println(m.get("scoreList"));
String[] stLists = m.get("stList").split(",");
for(int i=0;i<roundLists.length;i++){
map.put(roundLists[i],stLists[i]);
} /*for (Map.Entry<String, String> entry : map.entrySet()) {
System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue()+"");
}*/// System.out.println(map.size());
mapList.add(map);
} return mapList;
}}
参考链接： https://www.cnblogs.com/xiaoxi/p/7151433.html
