你的位置:首页 > Java教程

[Java教程]mysql 列转行、列转行的问题


     今天在逛大Java吧的时候看到一个行转列、列转行的问题,看了之后还真的不知道怎么下手,可能平时用hibernate作为持久层用多了,sql语句也不会写了,赶紧去找度娘聊聊天,然后建了个表做个测试,趁着这会儿还没忘改进总结一下,省的回头又忘了。

 

  首先需要新建一个表:

DROP TABLE IF EXISTS `test1`;CREATE TABLE `test1` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `ye` varchar(20) DEFAULT NULL, `me` varchar(20) DEFAULT NULL, `Scount` double DEFAULT NULL, PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

插入几条数据后效果如下所示:

最后要实现的效果是什么样的呢?如下所示:

 

要达到的效果就是如上所示的这样,那么怎么实现呢?SQL语句如下所示:

SELECT ye,(MAX(CASE me WHEN '1' THEN Scount ELSE 0 END)) AS M1,(MAX(CASE me WHEN '2' THEN Scount ELSE 0 END)) AS M2,(MAX(CASE me WHEN '3' THEN Scount ELSE 0 END)) AS M3,(MAX(CASE me WHEN '4' THEN Scount ELSE 0 END)) AS M4FROM test1 GROUP BY ye;

我暂时就这样写了,应该还有其他的方法,这个里面还得再说明几点;

1.case me when '1' then Scount else 0 end 其实就是一个条件判断,等同于 if(me == '1'){Scount}else{0},因此也可以使用SQL中的IF表达式来代替这个表达,使用if表达式怎么写呢?看下面:

SELECT ye,(MAX(IF(me='1',Scount,0))) AS M1,(MAX(IF(me='2',Scount,0))) AS M2,(MAX(IF(me='3',Scount,0))) AS M3,(MAX(IF(me='4',Scount,0))) AS M4FROM test1 GROUP BY ye;

if(expr1,expr2,expr3)表达式中,如果expr1表达式为true,那么结果就是expr2,如果expr1表达式为false,那么结果就是expr3;

2.在看列转行的时候看到这么一个函数group_concat(),觉得挺好玩的,就也记录一下吧:

SELECT ye,GROUP_CONCAT(me,'数量为:',Scount) AS MFROM test1 GROUP BY ye;

得到的结果如下:

其实就是将三个字段组合到了一起,这个函数具体用法为group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']),即还可以对字段进行排序以及使用特定的符号分隔数据;

说到group_concat了,那就把相关的concat()也给复习一下吧,concat(str1,str2,str3.....)就是把后面的str进行连接到一起,看如下例子:

SELECT CONCAT("aa","bb","cc") AS result;

查询结果如下:

但是str不能使null,如果是null的话,那么返回的结果就是null,例子我就不写了。

下一个是concat_ws(Separator ,str1,str2.....),这个函数就是使用第一个分隔符把后面的str连接起来,这个分隔符可以使符号也可以使字符串,下面给个例子:

可以看到,三个字符串使用_888_进行了连接,在这个函数中,Separator 可以为空字符串,但是不能为NULL,否则结果也是NULL,str可以为Null.

 

关于列转行,就使用concat的方式来进行查询,先对刚才的数据表进行一下修改,表结构不变,把数据修改一下,如下:

 

此时,按照年份求和,sql语句如下所示:

SELECT t.ye,CONCAT(  '1的总和',CAST(SUM(CASE t.me WHEN '1' THEN t.Scount ELSE 0 END) AS CHAR)) AS M1,CONCAT(  '2的总和',CAST(SUM(CASE t.me WHEN '2' THEN t.Scount ELSE 0 END) AS CHAR)  ) AS m2FROM test1 tGROUP BY t.ye;

查询结果如下:

 

  好了,行转列,列转行暂时就这样了,下次再见。