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行转列的资料请关注毛票票其它相关文章!