mysql行转列sql

创建学生表

create table t_student(id intauto_increment primary key ,city_no varchar(30),city_name varchar(30),city_type varchar(30),p_city_no varchar(30),stu_no varchar(30),stu_name varchar(30),stu_grade_no varchar(30),stu_grade_name varchar(30),stu_class_no varchar(30),stu_class_name varchar(30),exam_time varchar(30),course varchar(255),score int);

添加数据

ｉｎｓｅｒｔ INTO `test_db`.`t_student` (`id`, `city_no`, `city_name`, `city_type`, `p_city_no`, `stu_no`, `stu_name`, `stu_grade_no`, `stu_grade_name`, `stu_class_no`, `stu_class_name`, `exam_time`, `course`, `score`) VALUES (‘1‘, ‘1001‘, ‘福建省‘, ‘2‘, ‘1‘, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);ｉｎｓｅｒｔ INTO `test_db`.`t_student` (`id`, `city_no`, `city_name`, `city_type`, `p_city_no`, `stu_no`, `stu_name`, `stu_grade_no`, `stu_grade_name`, `stu_class_no`, `stu_class_name`, `exam_time`, `course`, `score`) VALUES (‘2‘, ‘100101‘, ‘厦门市‘, ‘3‘, ‘1001‘, ‘202001‘, ‘夏依‘, ‘1‘, ‘三年级‘, ‘1‘, ‘一班‘, ‘202201‘, ‘语文‘, ‘99‘);ｉｎｓｅｒｔ INTO `test_db`.`t_student` (`id`, `city_no`, `city_name`, `city_type`, `p_city_no`, `stu_no`, `stu_name`, `stu_grade_no`, `stu_grade_name`, `stu_class_no`, `stu_class_name`, `exam_time`, `course`, `score`) VALUES (‘3‘, ‘10010101‘, ‘思明区‘, ‘4‘, ‘100101‘, ‘202002‘, ‘思宁‘, ‘1‘, ‘三年级‘, ‘1‘, ‘一班‘, ‘202202‘, ‘语文‘, ‘101‘);ｉｎｓｅｒｔ INTO `test_db`.`t_student` (`id`, `city_no`, `city_name`, `city_type`, `p_city_no`, `stu_no`, `stu_name`, `stu_grade_no`, `stu_grade_name`, `stu_class_no`, `stu_class_name`, `exam_time`, `course`, `score`) VALUES (‘4‘, ‘10010102‘, ‘湖里区‘, ‘4‘, ‘100101‘, ‘202003‘, ‘胡明‘, ‘1‘, ‘三年级‘, ‘1‘, ‘一班‘, ‘202201‘, ‘语文‘, ‘111‘);ｉｎｓｅｒｔ INTO `test_db`.`t_student` (`id`, `city_no`, `city_name`, `city_type`, `p_city_no`, `stu_no`, `stu_name`, `stu_grade_no`, `stu_grade_name`, `stu_class_no`, `stu_class_name`, `exam_time`, `course`, `score`) VALUES (‘5‘, ‘10010103‘, ‘同安区‘, ‘4‘, ‘100101‘, ‘202004‘, ‘安然‘, ‘1‘, ‘三年级‘, ‘2‘, ‘二班‘, ‘202201‘, ‘数学‘, ‘120‘);ｉｎｓｅｒｔ INTO `test_db`.`t_student` (`id`, `city_no`, `city_name`, `city_type`, `p_city_no`, `stu_no`, `stu_name`, `stu_grade_no`, `stu_grade_name`, `stu_class_no`, `stu_class_name`, `exam_time`, `course`, `score`) VALUES (‘6‘, ‘10010104‘, ‘集美区‘, ‘4‘, ‘100101‘, ‘202005‘, ‘郭美美‘, ‘1‘, ‘三年级‘, ‘1‘, ‘一班‘, ‘202202‘, ‘数学‘, ‘103‘);ｉｎｓｅｒｔ INTO `test_db`.`t_student` (`id`, `city_no`, `city_name`, `city_type`, `p_city_no`, `stu_no`, `stu_name`, `stu_grade_no`, `stu_grade_name`, `stu_class_no`, `stu_class_name`, `exam_time`, `course`, `score`) VALUES (‘7‘, ‘10010105‘, ‘海沧区‘, ‘4‘, ‘100101‘, ‘202006‘, ‘李沧海‘, ‘1‘, ‘三年级‘, ‘1‘, ‘一班‘, ‘202202‘, ‘英语‘, ‘50‘);ｉｎｓｅｒｔ INTO `test_db`.`t_student` (`id`, `city_no`, `city_name`, `city_type`, `p_city_no`, `stu_no`, `stu_name`, `stu_grade_no`, `stu_grade_name`, `stu_class_no`, `stu_class_name`, `exam_time`, `course`, `score`) VALUES (‘8‘, ‘10010106‘, ‘翔安区‘, ‘4‘, ‘100101‘, ‘202007‘, ‘李翔‘, ‘1‘, ‘三年级‘, ‘2‘, ‘二班‘, ‘202202‘, ‘英语‘, ‘90‘);

原始数据：

原始数据

第一种方法：

-- 使用case when then else，这里使用sum函数也可以ｓｅｌｅｃｔ stu_name,max(case course when ‘语文‘ then score else 0 end)as ‘chinese‘,max(case course when ‘数学‘ then score else 0 end)as ‘math‘,max(case course when ‘英语‘ then score else 0 end)as ‘english‘,max(case course when ‘物理‘ then score else 0 end)as ‘wuli‘,max(case course when ‘化学‘ then score else 0 end)as ‘huaxue‘from t_student group by stu_name;

结果：

方法一结果集

第二种方法：

-- 使用if语句，这里使用sum函数也可以ｓｅｌｅｃｔ stu_name,max(if(course = ‘语文‘ and stu_grade_no=1,score,0))as ‘chinese‘,max(if(course = ‘数学‘ and stu_grade_no=1,score,0))as ‘math‘,max(if(course = ‘英语‘,score,0))as ‘english‘,max(if(course = ‘物理‘,score,0))as ‘wuli‘,max(if(course = ‘化学‘,score,0))as ‘huaxue‘from t_student group by stu_name;

结果：

方法二结果集

第三种方法：

-- 动态拼接sql语句，不管多少行都会转列set @sql = null;ｓｅｌｅｃｔ group_concat(distinct ｃｏｎｃａｔ(‘max(if(a.course = ‘‘‘,a.course,‘‘‘, a.score, 0)) as ‘‘‘,a.course, ‘‘‘‘)) into @sql from t_student a;set @sql = ｃｏｎｃａｔ(‘ｓｅｌｅｃｔ stu_name,‘, @sql, ‘from t_student a group by a.stu_name‘ );prepare stmt from @sql;-- 动态生成脚本，预备一个语句execute stmt;-- 动态执行脚本，执行预备的语句deallocate prepare stmt; -- 释放预备的语句 -- 通过这个查询拼接的sqlselect @sql

结果：

方法三结果集

第四种方法：

-- 使用distinctselect distincta.name,(ｓｅｌｅｃｔ score from test1 b where a.name=b.name and b.course=‘语文‘ ) as ‘chinese‘,(ｓｅｌｅｃｔ score from test1 b where a.name=b.name and b.course=‘数学‘ ) as ‘math‘,(ｓｅｌｅｃｔ score from test1 b where a.name=b.name and b.course=‘英语‘ ) as ‘english‘,(ｓｅｌｅｃｔ score from test1 b where a.name=b.name and b.course=‘物理‘ ) as ‘wuli‘,(ｓｅｌｅｃｔ score from test1 b where a.name=b.name and b.course=‘化学‘ ) as ‘huaxue‘from t_student a

结果：

方法四结果集

第五种方法：

以下三种方法是可以做统计的用的，有三种统计写法，有需求的话可以使用

-- 使用with rollup统计第一种ｓｅｌｅｃｔ ifnull(stu_name,‘总计‘) as stu_name,max(if(course = ‘语文‘,score,0))as ‘chinese‘,max(if(course = ‘数学‘,score,0))as ‘math‘,max(if(course = ‘英语‘,score,0))as ‘english‘,max(if(course = ‘物理‘,score,0))as ‘wuli‘,max(if(course = ‘化学‘,score,0))as ‘huaxue‘,sum(IF(course=‘total‘,score,0)) as ‘total‘from (ｓｅｌｅｃｔ stu_name,ifnull(course,‘total‘) as course,sum(score) as scorefrom t_student group by stu_name, course with rollup having stu_name is not null )as a group by stu_name with rollup;-- 使用with rollup与union all统计第二种ｓｅｌｅｃｔ stu_name,max(if(course = ‘语文‘,score,0))as ‘chinese‘,max(if(course = ‘数学‘,score,0))as ‘math‘,max(if(course = ‘英语‘,score,0))as ‘english‘,max(if(course = ‘物理‘,score,0))as ‘wuli‘,max(if(course = ‘化学‘,score,0))as ‘huaxue‘,sum(score) as total from t_student group by stu_nameunion allselect ‘total‘,max(if(course = ‘语文‘,score,0))as ‘chinese‘,max(if(course = ‘数学‘,score,0))as ‘math‘,max(if(course = ‘英语‘,score,0))as ‘english‘,max(if(course = ‘物理‘,score,0))as ‘wuli‘,max(if(course = ‘化学‘,score,0))as ‘huaxue‘,sum(score) from t_student-- 使用if与with rollup统计ｓｅｌｅｃｔ ifnull(stu_name,‘total‘) as stu_name,max(if(course = ‘语文‘,score,0))as ‘chinese‘,max(if(course = ‘数学‘,score,0))as ‘math‘,max(if(course = ‘英语‘,score,0))as ‘english‘,max(if(course = ‘物理‘,score,0))as ‘wuli‘,max(if(course = ‘化学‘,score,0))as ‘huaxue‘,sum(score) AS total from t_studentgroup by stu_name with rollup

结果： 以上三种语句结果都是一样的

方法五结果集