SQL行列转换问题

1、列转行

• 建表语句

DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `course` varchar(50) DEFAULT NULL COMMENT '课程',
  `score` int(3) DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO `t_student` VALUES (1,'张三', '语文', 99);
INSERT INTO `t_student` VALUES (2,'李四', '语文', 99);
INSERT INTO `t_student` VALUES (3,'王五', '语文', 80);
INSERT INTO `t_student` VALUES (4,'张三', '数学', 86);
INSERT INTO `t_student` VALUES (5,'李四', '数学', 96);
INSERT INTO `t_student` VALUES (6,'王五', '数学', 81);
INSERT INTO `t_student` VALUES (7,'张三', '英语', 78);
INSERT INTO `t_student` VALUES (8,'李四', '英语', 88);
INSERT INTO `t_student` VALUES (9,'王五', '英语', 88);
INSERT INTO `t_student` VALUES (10,'张三', '历史', 98);
INSERT INTO `t_student` VALUES (11,'李四', '历史', 85);
INSERT INTO `t_student` VALUES (12,'王五', '历史', 89);
INSERT INTO `t_student` VALUES ('13', '赵六', '语文', '90');
INSERT INTO `t_student` VALUES ('14', '赵六', '数学', '80');
INSERT INTO `t_student` VALUES ('15', '赵六', '英语', '60');
INSERT INTO `t_student` VALUES ('16', '赵六', '历史', '82');

• max(case when) as ’ ’

select
	name,
    max(case when course='语文' then score end) as '语文',
    max(case when course='数学' then score end) as '数学',
    max(case when course='英语' then score end) as '英语',
    max(case when course='历史' then score end) as '历史'
from t_student
group by name;

2、行转列

• 建表语句

DROP TABLE IF EXISTS `t_course`;
CREATE TABLE `t_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
  `chinese` double DEFAULT NULL COMMENT '语文成绩',
  `math` double DEFAULT NULL COMMENT '数学成绩',
  `english` double DEFAULT NULL COMMENT '英语成绩',
  `history` double DEFAULT NULL COMMENT '历史成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO t_course VALUES ('1', '张三', '100', '86', '98', '98');
INSERT INTO t_course VALUES ('2', '李四', '99', '99', '88', '65');
INSERT INTO t_course VALUES ('3', '王五', '80', '85', '80', '89');

• union 或者 union all

select
	user_name as name,'语文' as course,chinese as score
from t_course
union all
select
	user_name as name,'数学' as course,math as score
from t_course
union all
select
	user_name as name,'英语' as course,english as score
from t_course
union all
select
	user_name as name,'历史' as course,history as score
from t_course;

3、列转字符串 group_concat()

select
	name,
    group_concat(course,':',score)
from t_student
group by name;

4、字符串转列

• 建表语句

DROP TABLE IF EXISTS `t_user_order`;
CREATE TABLE `t_user_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
  `user_id` varchar(50) DEFAULT NULL COMMENT '用户 id',
  `order_id` varchar(100) DEFAULT NULL COMMENT '订单 ids',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO t_user_order VALUES ('1', 'user1', '1,4,7,10,13');
INSERT INTO t_user_order VALUES ('2', 'user2', '2,5,8,11,14');
INSERT INTO t_user_order VALUES ('3', 'user3', '3,6,9,12,15');

• SUBSTRING_INDEX()

SELECT
    a.user_id,
    SUBSTRING_INDEX( SUBSTRING_INDEX( a.order_id, ',', b.help_topic_id + 1 ), ',',- 1 ) AS order_id
FROM
    t_user_order AS a
LEFT JOIN mysql.help_topic AS b ON b.help_topic_id < ( length( a.order_id ) - length( REPLACE ( a.order_id, ',', '' ) ) + 1 );