MYSQL的动态行转列（非技术向，偏实用向）

前言

本人刚进入企业实习开发后端，首先学习的便是数据库。大学虽然各种方面都学过，但实则外强中干，一上来就被前辈教给我的学习任务难倒了，就是sql中的行转列。虽然网上已经有很多人写过行转列的文章了，但可能是因为我比较菜所以整了两天才整出来，但为了让自己能够加深印象并且方便他人能看完这篇文章的5分钟内就把行转列完成，我写下了这篇文章。

正文

先上数据库表格

course_grade

course_info

teacher_info

三张数据表，有些人可能会觉得这是他第一次见到这么杂乱无章并且难看的数据表，我一开始也是这么觉得的，但是前辈告诉我，数据表里的字段再多再乱也有可能，我们只要把我们要的字段提取出来就行，那他再乱也没事。这几张表本来就是前辈随手给我做的，再加上因为数据量不够以及呈现效果不好给我随便加了几条数据变成了这样。

目标

能够显示出每个老师所有课程级别的开课数量。 然后关键是course_grade表中的课程级别可能会因客户的要求增加或减少，但是我要在不更改sql语句的情况下能正常使用。

先上代码

别随便套sum()!别随便套sum()!别随便套sum()! 原因在下面写着。

SET @SQL = NULL;
SELECT
    GROUP_CONCAT(
        CONCAT(
            'sum(IF(course_grade.course_gra = ''',
            course_grade.course_gra,
            ''',1, 0)) AS ''',
            course_grade.course_gra,
            ''''
        )
    ) INTO @SQL
FROM
    course_grade
GROUP BY
    @SQL.NAME;
SET @SQL = CONCAT(
    'SELECT teacher_info.name AS name,',
    @SQL,
    ' FROM teacher_info
    LEFT JOIN course_grade ON (1 = 1)    
    inner JOIN course_info ON course_info.teacher_id = teacher_info.id
    AND course_grade.id = course_info.course_grade    
GROUP BY teacher_info.name'
);
PREPARE stmt
FROM
    @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
 

应用时的替换方法

先从下面的语句连接开始

concat('   ')中的语句很简单，你只要先将要连接的多张表用sql语句运行没问题之后，直接复制粘贴再改一点点就行了，具体看下面的详解。

 

SET @SQL = CONCAT(
    'SELECT teacher_info.name AS name              

 //这个concat中的select中的字段，是你要展示的除了需要动态赋值以外的字段，就比如说是姓名、年龄那些一条记录中就算行转列后也不会改变的字段。
 ,',@SQL,' 

//上面这个  ,',@SQL,'  千万不要忘记，因为没有的话这个concat的分号中的内容就是一个普通的查询语句
    FROM teacher_info
    LEFT JOIN course_grade ON (1 = 1)    
    inner JOIN course_info ON course_info.teacher_id = teacher_info.id
    AND course_grade.id = course_info.course_grade    

//普通的多表联查
GROUP BY teacher_info.name'

//group by的排序很重要，如果结果和你预期的对不上基本上就是group by后面的字段跟错了，出错后推荐先尝试改这里
);

接下来讲下动态行转列的关键

SET @SQL = NULL;
SELECT
    GROUP_CONCAT(
        CONCAT(
            'sum(IF(course_grade.course_gra = ''',course_grade.course_gra,'''

//特意断了个句应该就能看懂了吧，我们需要动态的目标就是这个''',course_grade.course_gra,'''      ，''', ,'''是用来包裹目标字段的存在，因为美化sql和别的文章都没有特地指出，我一开始学动态行转列的时候都没发现。

            ,1, 0)) AS

//"1"这个值的位置，就是你们要行转列后显示在表中的数据。我是因为要显示课程数量，所以在写了个1用来sum。如果你们是成绩，那就填（成绩表.成绩）这样懂吧。

             ''', course_grade.course_gra,'''

//所以在套用这段代码的时候这三个course_grade.course_gra就要替换成你想要转的字段名。

//别随便套sum()!别随便套sum()!别随便套sum()!

//特意说了好多遍，原因是我的代码是要显示总课程数，所以才在if()外面套了个sum()。而你们在写的时候一定要注意你们要展示给别人看什么。比如说要给展示一个人莫门课成绩最高的一次，那就把sum()改成max。或者也有可能根本就不需要在if()外面套函数，所以一定要根据情况自己判断一下。

'
        )
    ) INTO @SQL
FROM
    course_grade

//这张表总不用说了吧，不然就别做程序员了。
GROUP BY
    @SQL.NAME;

 

 

最后一段就直接复制粘贴就完了。

PREPARE stmt
FROM
    @SQL;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

 

希望我的这篇文章能对各位有帮助。