题目描述
表格设计
/*
SQLyog 企业版 - MySQL GUI v8.14
MySQL - 5.5.27
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `demo1` (
`teacherId` double ,
`dayId` double ,
`boolke` varchar (384)
);
insert into `demo1` (`teacherId`, `dayId`, `boolke`) values('1','2','有');
insert into `demo1` (`teacherId`, `dayId`, `boolke`) values('1','3','有');
insert into `demo1` (`teacherId`, `dayId`, `boolke`) values('2','1','有');
insert into `demo1` (`teacherId`, `dayId`, `boolke`) values('3','2','有');
insert into `demo1` (`teacherId`, `dayId`, `boolke`) values('1','2','有');
解题方案
这个题的思路在于首先需要根据teacherId和dayId进行分组获取其次数,然后使用case结合max进行行列转换即可。
SELECT
info.teacherId,
MAX(
CASE info.dayId
WHEN 1 THEN
info.c
ELSE
0
END
) AS '星期一',
MAX(
CASE info.dayId
WHEN 2 THEN
info.c
ELSE
0
END
) AS '星期二',
MAX(
CASE info.dayId
WHEN 3 THEN
info.c
ELSE
0
END
) AS '星期三'
FROM
(
SELECT
teacherId,
dayId,
count(dayId) AS c
FROM
demo1
WHERE
boolke = '有'
GROUP BY
teacherId,
dayId
) AS info
GROUP BY
info.teacherId