MySQL 中行转列的方法
MySQL行转列操作
所谓的行转列操作，就是将一个表的行信息转化为列信息，说着可能比较笼统，这里先举个例子，如下：
+----+-----------+--------+-------+ |ID|USER_NAME|COURSE|SCORE| +----+-----------+--------+-------+ ||张三|数学|| ||张三|语文|| ||张三|英语|| ||李四|数学|| ||李四|语文|| ||李四|英语|| ||王五|数学|| ||王五|语文|| ||王五|英语|| +----+-----------+--------+-------+ rowsinset(0.00sec) +-----------+--------+--------+--------+ |user_name|数学|语文|英语| +-----------+--------+--------+--------+ |张三|||| |李四|||| |王五|||| +-----------+--------+--------+--------+ rowsinset(0.00sec)
上面的例子中，表1给出了三个学生的三门成绩，而表2是将表1的行记录信息(学科、姓名)转化为列信息，并根据不同的user_name进行分组显示。
1casewhen操作方法
要实现上面的功能，我们需要进行分析，首先，我们需要生成三个列，分别是数学，语文和英语，然后给每个列中的值填入对应的数据。这里需要用到mysql的casewhenthenend操作，也就是条件操作，关于这个条件语句，首先我们给出解释：
casecolume whencondition1thenresult1 whencondition2thenresult2 whencondition3thenresult3 elseresult4 end
上面的语法，可以理解为当column的值符合condition1的时候，用result1去替换column的值，以此类推，当column值都不符合的时候，用result4去替换column的值。
现在开始试验：
首先我们创建一张表，并插入如下数据：
mysql-yeyz::>>select*fromtest_tbl; +----+-----------+--------+-------+ |ID|USER_NAME|COURSE|SCORE| +----+-----------+--------+-------+ ||张三|数学|| ||张三|语文|| ||张三|英语|| ||李四|数学|| ||李四|语文|| ||李四|英语|| ||王五|数学|| ||王五|语文|| ||王五|英语|| +----+-----------+--------+-------+ rowsinset(0.00sec)
根据上面casewhen语法，当碰到课程为'数学'的时候，我们定义一个列'数学'，并把它的score填入其中，如果碰到'语文'或者'英语'，那么把它替换为0，我们可以先笼统的写出如下SQL:
mysql-yeyz::>>SELECTuser_name, (CASEcourseWHEN'数学'THENscoreELSEEND)数学 FROMtest_tbl; +-----------+--------+ |user_name|数学| +-----------+--------+ |张三|| |张三|| |张三|| |李四|| |李四|| |李四|| |王五|| |王五|| |王五|| +-----------+--------+ rowsinset(0.00sec)
我们发现上面的表只有两个列，根据SQL规则，我们可以把'语文'和'英语'的值也加入进去，一次性多写几个列，如下：
mysql-yeyz::>>SELECTuser_name, ->(CASEcourseWHEN'数学'THENscoreELSEEND)数学, ->(CASEcourseWHEN'语文'THENscoreELSEEND)语文, ->(CASEcourseWHEN'英语'THENscoreELSEEND)英语 ->FROMtest_tbl; +-----------+--------+--------+--------+ |user_name|数学|语文|英语| +-----------+--------+--------+--------+ |张三|||| |张三|||| |张三|||| |李四|||| |李四|||| |李四|||| |王五|||| |王五|||| |王五|||| +-----------+--------+--------+--------+ rowsinset(0.00sec)
这下好了，我们有了所有的记录了，已经快要达到我们的目的了，看看和最终结果的差距：
+-----------+--------+--------+--------+ |user_name|数学|语文|英语| +-----------+--------+--------+--------+ |张三|||| |李四|||| |王五|||| +-----------+--------+--------+--------+
好像就剩把那些同名的学生信息都合并一下就好了，自然而然我们想到了group_by(user_name)的操作，而group_by操作需要和一些聚合函数(MAX,MIN,AVG,SUM，COUNT等)进行搭配。由于每条记录中只包含当前学科的成绩，其他学科的成绩为0，所以我们使用MAX函数和SUM函数的结果是相同的，但是不能使用AVG函数和MIN函数，这应该很好理解吧。
下面我们给出最终结果：
mysql-yeyz13:55:52>>SELECTuser_name, ->MAX(CASEcourseWHEN'数学'THENscoreELSEEND)数学, ->MAX(CASEcourseWHEN'语文'THENscoreELSEEND)语文, ->MAX(CASEcourseWHEN'英语'THENscoreELSEEND)英语 ->FROMtest_tbl ->GROUPBYUSER_NAME; +-----------+--------+--------+--------+ |user_name|数学|语文|英语| +-----------+--------+--------+--------+ |张三|34|58|58| |李四|45|87|45| |王五|76|34|89| +-----------+--------+--------+--------+ 3rowsinset(0.00sec) mysql-yeyz::>>SELECTuser_name, ->sum(CASEcourseWHEN'数学'THENscoreELSEEND)数学, ->sum(CASEcourseWHEN'语文'THENscoreELSEEND)语文, ->sum(CASEcourseWHEN'英语'THENscoreELSEEND)英语 ->FROMtest_tbl ->GROUPBYUSER_NAME; +-----------+--------+--------+--------+ |user_name|数学|语文|英语| +-----------+--------+--------+--------+ |张三|34|58|58| |李四|45|87|45| |王五|76|34|89| +-----------+--------+--------+--------+ 3rowsinset(0.00sec) mysql-yeyz::>>SELECTuser_name, ->MIN(CASEcourseWHEN'数学'THENscoreELSEEND)数学, ->MIN(CASEcourseWHEN'语文'THENscoreELSEEND)语文, ->MIN(CASEcourseWHEN'英语'THENscoreELSEEND)英语 ->FROMtest_tbl ->GROUPBYUSER_NAME; +-----------+--------+--------+--------+ |user_name|数学|语文|英语| +-----------+--------+--------+--------+ |张三|0|0|0| |李四|0|0|0| |王五|0|0|0| +-----------+--------+--------+--------+ 3rowsinset(0.00sec)
可以看出来，使用MAX和使用SUM的结果是一样的，但是使用MIN作为聚合函数，会导致最终输出的结果都是0，因为每次都选的是该user_name指定学科的最小的值，也就是0。这样的结果就很好理解了。
2if操作方法
上面的case when操作方法理解了，那么if的操作方法也很好理解，原理是一样的，只不过是把casewhen的语法转换为if方式，如下
mysql-yeyz14:12:42>>SELECTuser_name, ->MAX(if(course='数学',score,))数学, ->MAX(if(course='语文',score,))语文, ->MAX(if(course='英语',score,))英语 ->FROMtest_tbl ->GROUPBYUSER_NAME; +-----------+--------+--------+--------+ |user_name|数学|语文|英语| +-----------+--------+--------+--------+ |张三|34|58|58| |李四|45|87|45| |王五|76|34|89| +-----------+--------+--------+--------+ 3rowsinset(0.00sec)
3添加total列
当我们把基本的行转列实现之后，我们现在需要在转换之后的表上面添加一个total字段，这个字段的添加我们可以通过下面的方法，即在最开始统计的时候，就把score值也统计进去，如下：
mysql-yeyz14:18:06>>SELECTuser_name, ->(CASEcourseWHEN'数学'THENscoreELSEEND)数学, ->(CASEcourseWHEN'语文'THENscoreELSEEND)语文, ->(CASEcourseWHEN'英语'THENscoreELSEEND)英语, ->(score)total ->FROMtest_tbl; +-----------+--------+--------+--------+-------+ |user_name|数学|语文|英语|total| +-----------+--------+--------+--------+-------+ |张三|34|0|0|34| |张三|0|58|0|58| |张三|0|0|58|58| |李四|45|0|0|45| |李四|0|87|0|87| |李四|0|0|45|45| |王五|76|0|0|76| |王五|0|34|0|34| |王五|0|0|89|89| +-----------+--------+--------+--------+-------+ 9rowsinset(0.00sec)
上面的结果是没有聚合后的结果，这里需要注意的是，如果我们要聚合，前三个列可以使用sum或者max的方法，最后一个列一定要使用sum的方法，因为我们要求的是总成绩，使用max的方法会导致取值变为分数最高的那个值。最后的sql如下：
mysql-yeyz14:18:29>>SELECTuser_name, ->sum(CASEcourseWHEN'数学'THENscoreELSEEND)数学, ->sum(CASEcourseWHEN'语文'THENscoreELSEEND)语文, ->sum(CASEcourseWHEN'英语'THENscoreELSEEND)英语, ->sum(score)total ->FROMtest_tbl ->GROUPBYUSER_NAME; +-----------+--------+--------+--------+-------+ |user_name|数学|语文|英语|total| +-----------+--------+--------+--------+-------+ |张三|34|58|58|150| |李四|45|87|45|177| |王五|76|34|89|199| +-----------+--------+--------+--------+-------+ 3rowsinset(0.00sec)
4简单方法group_concat
如果我们对于结果的显示格式要求不是那么细致的话，也可以用一种粗犷的方法，就是group_concat函数，将所有的列都写在一起，用一个字段表示，效果如下：
mysql-yeyz14:19:13>>SELECTuser_name, GROUP_CONCAT(`course`,":",score)AS成绩 FROMtest_tbl GROUPBYuser_name; +-----------+-------------------------------+ |user_name|成绩| +-----------+-------------------------------+ |张三|数学:34,语文:58,英语:58| |李四|数学:45,语文:87,英语:45| |王五|数学:76,语文:34,英语:89| +-----------+-------------------------------+ 3rowsinset(0.00sec)
这种方法相当于直接针对原始表做了一个分组，也能够应付一定的应用场景。
以上就是MySQL中行转列的方法的详细内容，更多关于MySQL行转列的资料请关注毛票票其它相关文章！