表的行转列

题目描述

表格设计

/*
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