1.建表tb_year_order:
CREATE TABLE `tb_year_order` (
`id` int(11) NOT NULL,
`year` varchar(255) DEFAULT NULL,
`mouth` varchar(255) DEFAULT NULL,
`count_order` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
2.插入数据:
INSERT INTO `gogogo`.`tb_year_order`(`id`, `year`, `mouth`, `count_order`) VALUES (1, '2022', '1', '88');
INSERT INTO `gogogo`.`tb_year_order`(`id`, `year`, `mouth`, `count_order`) VALUES (2, '2022', '2', '89');
INSERT INTO `gogogo`.`tb_year_order`(`id`, `year`, `mouth`, `count_order`) VALUES (3, '2022', '3', '90');
INSERT INTO `gogogo`.`tb_year_order`(`id`, `year`, `mouth`, `count_order`) VALUES (4, '2023', '1', '66');
INSERT INTO `gogogo`.`tb_year_order`(`id`, `year`, `mouth`, `count_order`) VALUES (5, '2023', '2', '67');
INSERT INTO `gogogo`.`tb_year_order`(`id`, `year`, `mouth`, `count_order`) VALUES (6, '2023', '3', '68');
3.题目:
3.1生成的表:
3.2要求:查询出以下的结果??????
4.实现:
SELECT year,
max(CASE when mouth=1 then count_order end) '1月',
max(CASE when mouth=2 then count_order end) '2月',
max(CASE when mouth=3 then count_order end) '3月'
from tb_year_order GROUP BY year;