# Write your MySQL query statement below
1179. 重新格式化部门表
部门表 Department：
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | revenue | int | | month | varchar | +---------------+---------+ (id, month) 是表的联合主键。 这个表格有关于每个部门每月收入的信息。 月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。
编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。
查询结果格式如下面的示例所示：
Department 表： +------+---------+-------+ | id | revenue | month | +------+---------+-------+ | 1 | 8000 | Jan | | 2 | 9000 | Jan | | 3 | 10000 | Feb | | 1 | 7000 | Feb | | 1 | 6000 | Mar | +------+---------+-------+ 查询得到的结果表： +------+-------------+-------------+-------------+-----+-------------+ | id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue | +------+-------------+-------------+-------------+-----+-------------+ | 1 | 8000 | 7000 | 6000 | ... | null | | 2 | 9000 | null | null | ... | null | | 3 | null | 10000 | null | ... | null | +------+-------------+-------------+-------------+-----+-------------+ 注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。
原站题解
pythondata 解法, 执行用时: 359 ms, 内存消耗: 66.9 MB, 提交时间: 2024-05-27 13:02:55
import pandas as pd
import numpy as np
def reformat_table(department: pd.DataFrame) -> pd.DataFrame:
columns = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
return department.pivot(index='id', columns='month', values='revenue'). \
reindex(columns=columns).rename(columns=lambda x: f'{x}_Revenue'). \
reset_index()
oraclesql 解法, 执行用时: 1709 ms, 内存消耗: 0 B, 提交时间: 2020-07-30 23:49:59
/* Write your PL/SQL query statement below */
/* decode(value, if x, then x, if y, then y, if z, then z, ..., else) */
select id,
sum(decode(month, 'Jan', revenue, null)) Jan_Revenue,
sum(decode(month, 'Feb', revenue, null)) Feb_Revenue,
sum(decode(month, 'Mar', revenue, null)) Mar_Revenue,
sum(decode(month, 'Apr', revenue, null)) Apr_Revenue,
sum(decode(month, 'May', revenue, null)) May_Revenue,
sum(decode(month, 'Jun', revenue, null)) Jun_Revenue,
sum(decode(month, 'Jul', revenue, null)) Jul_Revenue,
sum(decode(month, 'Aug', revenue, null)) Aug_Revenue,
sum(decode(month, 'Sep', revenue, null)) Sep_Revenue,
sum(decode(month, 'Oct', revenue, null)) Oct_Revenue,
sum(decode(month, 'Nov', revenue, null)) Nov_Revenue,
sum(decode(month, 'Dec', revenue, null)) Dec_Revenue
from Department d
group by id;
mysql 解法, 执行用时: 395 ms, 内存消耗: 0 B, 提交时间: 2020-07-30 23:38:36
# Write your MySQL query statement below
# sum, case when ... then ... end
select id,
sum(case
when month = 'Jan'
then revenue
end) as Jan_Revenue,
sum(case
when month = 'Feb'
then revenue
end) as Feb_Revenue,
sum(case
when month = 'Mar'
then revenue
end) as Mar_Revenue,
sum(case
when month = 'Apr'
then revenue
end) as Apr_Revenue,
sum(case
when month = 'May'
then revenue
end) as May_Revenue,
sum(case
when month = 'Jun'
then revenue
end) as Jun_Revenue,
sum(case
when month = 'Jul'
then revenue
end) as Jul_Revenue,
sum(case
when month = 'Aug'
then revenue
end) as Aug_Revenue,
sum(case
when month = 'Sep'
then revenue
end) as Sep_Revenue,
sum(case
when month = 'Oct'
then revenue
end) as Oct_Revenue,
sum(case
when month = 'Nov'
then revenue
end) as Nov_Revenue,
sum(case
when month = 'Dec'
then revenue
end) as Dec_Revenue
from department
group by id;