LeetCode 1179.重新格式化部门表

最新推荐文章于 2024-05-30 14:59:09 发布

奋斗哼哼

最新推荐文章于 2024-05-30 14:59:09 发布

阅读量41

点赞数 1

文章标签：数据库 sql leetcode mysql

本文链接：https://blog.csdn.net/weixin_51696882/article/details/130472767

版权

数据准备

drop table Department;

Create table If Not Exists Department (id int, revenue int, month varchar(5));
Truncate table Department;
insert into Department (id, revenue, month) values ('1', '8000', 'Jan');
insert into Department (id, revenue, month) values ('2', '9000', 'Jan');
insert into Department (id, revenue, month) values ('3', '10000', 'Feb');
insert into Department (id, revenue, month) values ('1', '7000', 'Feb');
insert into Department (id, revenue, month) values ('1', '6000', 'Mar');

输入

在这里插入图片描述

输出

行转列,先求出所有数据

-- todo 1 行转列,先求出所有数据
select id,
   case
       when month='Jan' then revenue
   end as Jan_Revenue,
   case
       when month='Feb' then revenue
   end as Feb_Revenue,
   case
       when month='Mar' then revenue
   end as Mar_Revenue
from Department;

在这里插入图片描述

以id分组,求出每个月每个人的数据总和

-- todo 2 以id分组,求出每个月每个人的数据总和
with t1 as (
    select id,
       case
           when month='Jan' then revenue
       end as Jan_Revenue,
       case
           when month='Feb' then revenue
       end as Feb_Revenue,
       case
           when month='Mar' then revenue
       end as Mar_Revenue
    from Department
)
select id,
       sum(Jan_Revenue) as Jan_Revenue,
       sum(Feb_Revenue) as Feb_Revenue,
       sum(Mar_Revenue) as Mar_Revenue
from t1
group by id;