mysql中列转行

墨蓝 2023-06-01 12:49 4阅读 0赞

通过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

![993791-20190802162516997-1045010027.png][]

![993791-20190802162525361-70950136.png][]

### 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

转载于:https://www.cnblogs.com/itzlg/p/11289345.html

[993791-20190802162516997-1045010027.png]: /images/20230601/e0ec53eaea1a4db1bd73155237eced8f.png
[993791-20190802162525361-70950136.png]: /images/20230601/793d4f1a8bb54525829d4062227dc7ef.png