mysql中列转行

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

巴特西

mysql中列转行

1.mapper.xml文件

2.mapper文件

3.ServiceImpl.java

4.Service文件

5.Controller文件

6.ColToRowUtil.java

最新文章

热门文章