Mysql与Oracle关于行转列，列转行问题

Mysql实现

1、Mysql行转列

在接下来的例子中，我新建了一个学生成绩表，表SQL如下：

DROP TABLE IF EXISTS `stu`;

CREATE TABLE `stu` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `course` varchar(50) DEFAULT NULL,
  `score` double DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

/*Data for the table `stu` */
insert  into `stu`(`id`,`name`,`course`,`score`) values 
(1,'李明','数学',80);
insert  into `stu`(`id`,`name`,`course`,`score`) values 
(2,'李明','语文',99.9);
insert  into `stu`(`id`,`name`,`course`,`score`) values 
(3,'小明','外语',76);
insert  into `stu`(`id`,`name`,`course`,`score`) values 
(4,'小红','数学',100);
insert  into `stu`(`id`,`name`,`course`,`score`) values 
(5,'小红','语文',77);
insert  into `stu`(`id`,`name`,`course`,`score`) values 
(6,'小红','外语',99.6);

 通过 SELECT * FROM stu; 查出数据如下

 需求：按姓名分组，将同一学生的成绩通过分隔符分隔 再拼接在一起

1、多行转一列    

1.1、group_concat([DISTINCT] 目标字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

 可使用DISTINCT排除拼接结果中的重复值；Order BY对结果中的值进行排序；Separator表示分隔符，可自定义分隔符，不写默认为逗号。

使用此语句的步骤是先分组，在同一分组中将目标字段数据拼接在一起。SQL语句如下

SELECT NAME,GROUP_CONCAT(course SEPARATOR '-'),GROUP_CONCAT(score order by score) FROM stu group by name;

转换后的样式如下：

 1.2、可以通过concat()将字符串拼接到一起构造所需的数据。

 concat(str1,str2,st3,...)可以有很多个参数；但是参数若存在null，则结果也将返回null。  

case when语句估计有些朋友不懂，其实相当于if-else逻辑。

/* 相当于IF-ELSE逻辑，使用方式一 */
CASE 匹配字段1  WHEN 匹配的值1  THEN 返回的数据1 
CASE 匹配字段2  WHEN 匹配的值2  THEN 返回的数据1
......
[ELSE 默认值]
END

/* 相当于IF-ELSE逻辑，使用方式二 *
CASE WHEN 表达式1（比较1） THEN 返回数据1
CASE WHEN 表达式2（比较2） THEN 返回数据2
......
[ELSE 默认值]
END

整理后的的SQL如下：

SELECT NAME, CONCAT(
MAX(CASE course WHEN '数学' THEN score END),';',
MAX(CASE course WHEN '语文' THEN score END),';',
MAX(CASE course WHEN '外语' THEN score END)
) AS score
FROM stu GROUP BY NAME;

效果如下：

   

1.3、使用  CONCAT_WS(SEPARATOR,str1,str2,…)  语句实现，参数中传入分隔符SEPARATOR，使多个字符串拼接在一起。

SEPARATOR   表示分隔符； 可以有多个字符串拼接； 分隔符后填入null会自动被忽略。       整理的SQL如下：

SELECT NAME, CONCAT_WS('、',
MAX(CASE course WHEN '数学' THEN score END),
MAX(CASE course WHEN '语文' THEN score END),
MAX(CASE course WHEN '外语' THEN score END)
FROM stu GROUP BY NAME;

效果如下：

     

 

2、多行转多列    通过聚合函数MAX(),SUM()将数据由多行转多列

还是使用上面的表来举例吧。接下来实现将每个学生单独列一行，各科成绩按照科目排列。

实现思想：按照姓名先进行分组，将数学、语文、外语成绩由三行转为一列，可以通过聚合函数实现。SQL语句示例如下：

SELECT NAME,
MAX(CASE course WHEN '数学' THEN score END) AS 数学,
MAX(CASE course WHEN '语文' THEN score END) AS 语文,
SUM(CASE course WHEN '外语' THEN score END) AS 外语
FROM stu GROUP BY NAME;

效果如下：

 

2、Mysql列转行    通过使用Union将多次查询的数据合并到一起

此例需要不一样的表了，新建表的SQL如下：

DROP TABLE IF EXISTS `stu2`;

CREATE TABLE `stu2` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `Math` double DEFAULT '0',
  `Chinese` double DEFAULT '0',
  `English` double DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `stu2` */

insert  into `stu2`(`id`,`name`,`Math`,`Chinese`,`English`) values 
(1,'李明',79.5,84.5,99),
(2,'小红',78,65,100),
(3,'小花',77.7,88,100);

通过 SELECT * FROM stu2; 查到的数据如下：

                     

现在要实现将每科目成绩单独成行，效果如下：

                      

实现思想：每次查询只取一门学科的成绩，多查询几次，将几次查询的结果通过Union合并在一起。实现代码如下：

SELECT NAME, '语文' course ,Math AS score FROM stu2
UNION SELECT NAME, '数学' course ,Chinese AS score FROM stu2
UNION SELECT NAME, '英语' course ,English AS score FROM stu2

 

Oracle实现

1、Oracle行转列

1、多行转一行

Oracle建一张stu表存储学生的各科成绩，建表SQL和插入数据如下：

/* 建表结构 */
create table stu(
id number(10) primary key,
name varchar2(50),
course varchar2(20),
score number(3)
);

/* 插入数据 */
insert into stu values(1,'李明','数学',80);
insert into stu values(2,'李明','语文'92);
insert into stu values(3,'李明','外语',79);
insert into stu values(4,'小红','数学',59);
insert into stu values(5,'小红','语文',99);
insert into stu values(6,'小红','外语',95);
insert into stu values(7,'张斌','数学',100);
insert into stu values(8,'张斌','语文',85);
insert into stu values(9,'张斌','外语',95);

 通过 select * from stu order by id; 可以查看创建的表的结构如下：

接下来将同一学生不同科目合并到一行显示。

1.1、实现思想：  通过Oracle特有的字符串拼接符 || 将多个数据拼接连在一起，拼接成想要的数据。 其SQL语句如下：

select name,
max(decode(course,'数学',score,0)) ||','||
max(decode(course,'语文',score,0)) ||','||
max(decode(course,'外语',score,0)) as 科目成绩
from stu
group by name

效果如下：

1.2、通过wm_concat(column)实现多行转一行

第一次见过这种写法，来自于  Oracle行转列、列转行的Sql语句总结(转)   我也是偶然间看到这一篇博客才知道的。SQL语句如下：

select name, wm_concat(score) 成绩 from stu group by name;

/* 可以通过replace替换默认的逗号分隔符 */
select name,replace(wm_concat(score),',',';') 成绩 from stu group by name;

效果如下：

                     

2、多行转多列     

记得这是我第一次接触这东西，还是在第一家公司遇到的。当时项目做一个报表，需要显示月份、季度、半年、年度数据，看到需求就懵了，啥玩意啊，后来请教了个前辈才有了思路。

2.1、MAX结合CASE WHEN语句实现  ，SQL语句如下：

/* 标准的SQL语句写法 */
SELECT NAME,
MAX(CASE course WHEN '数学' THEN score END) AS 数学,
MAX(CASE course WHEN '语文' THEN score END) AS 语文,
SUM(CASE course WHEN '外语' THEN score END) AS 外语
FROM stu GROUP BY NAME;

效果如下图：

2.2、MAX结合DECODE（Oracle特有）实现，Oracle的DECODE语句相当于CASE WHEN语句 。SQL语句如下：

/* Oracle特有的写法  逻辑也一样，Oracle的DECODE语句相当于CASE WHEN语句 */
SELECT Name,
MAX(DECODE(course,'数学',score,0)) AS 数学,
MAX(DECODE(course,'语文',score,0)) AS 语文,
SUM(DECODE(course,'外语',score,0)) AS 外语
FROM stu GROUP BY Name;

效果和2.1一样。

2.3、通过pivot（聚合函数 for 列名 in（类型））实现  ，其中 in() 中可以指定别名，in中还可以指定子查询

SQL语句如下：

/* 数据显示范围 */
select * from (select name,COURSE,SCORE from stu) 
pivot(sum(score) for course in ('数学' as Math,'语文' as Chinese,'外语' as English) );

实现效果如下：

 

2、列转行           unpivot（科目  for  成绩  in（各门学科成绩））

创建表结构和数据如下SQL：

/* 创建表结构 */
create table stu2(
id number(5) primary key,
name varchar2(30),
Math number(3),
Chinese number(3),
English number(3)
);

/* 插入数据 */
insert into stu2 values(1,'李明',87,68,82);
insert into stu2 values(2,'小红',49,99,100);
insert into stu2 values(3,'张斌',55,68,99);

通过  select * from stu2;  查出数据如下所示：

实现思想：通过  unpivot（科目  for  成绩  in（各门学科成绩））  实现 ，SQL语句如下：

select name,course,grade from stu2
unpivot(grade for course in (Math,Chinese,English))

效果如下：