MySQL列转行

MySQL列转行

列：数据库中存在如下的数据

教师ID	星期	是否有课
1	1	1
1	2	1
2	1	1
3	4	1
4	1	1

统计出每个老师每个工作日的上课数，要求结构如下

教师ID	星期一	星期二	星期三	星期四
1	1	1	2	1
2	1	1	0	1

• 首先创建表

create table teacher (
 teacher_id int,
 week varchar(11),
 have_class TINYINT(1)
 );
 
 insert into teacher values(1,1,1);
 insert into teacher values(1,3,1);
 insert into teacher values(1,4,1);
 insert into teacher values(1,5,1);

 insert into teacher values(2,2,0);
 insert into teacher values(2,3,0);
 insert into teacher values(2,5,0);
 
 insert into teacher values(3,3,1);
 insert into teacher values(3,4,1);
 insert into teacher values(3,2,1);

• 查询语句

SELECT 
teacher_id as 教师号,
SUM(IF(week = 1,have_class,0)) 星期一,
SUM(IF(week = 2,have_class,0)) 星期二,
SUM(IF(week = 3,have_class,0)) 星期三,
SUM(IF(week = 4,have_class,0)) 星期四,
SUM(IF(week = 5,have_class,0)) 星期五,
SUM(IF(week = 6,have_class,0)) 星期六
 FROM teacher GROUP BY teacher_id

除此之外还可以使用

sum (
case week 
when 1 then have_class
else 0
end
)

• 查询结果