【leetcode】1179. Reformat Department Table
题目如下：
Table:
Department+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | revenue | int | | month | varchar | +---------------+---------+ (id, month) is the primary key of this table. The table has information about the revenue of each department per month. The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].
Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.
The query result format is in the following example:
Department table: +------+---------+-------+ | id | revenue | month | +------+---------+-------+ | 1 | 8000 | Jan | | 2 | 9000 | Jan | | 3 | 10000 | Feb | | 1 | 7000 | Feb | | 1 | 6000 | Mar | +------+---------+-------+ Result table: +------+-------------+-------------+-------------+-----+-------------+ | id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue | +------+-------------+-------------+-------------+-----+-------------+ | 1 | 8000 | 7000 | 6000 | ... | null | | 2 | 9000 | null | null | ... | null | | 3 | null | 10000 | null | ... | null | +------+-------------+-------------+-------------+-----+-------------+ Note that the result table has 13 columns (1 for the department id + 12 for the months).
解题思路：难得有免费的SQL题目。我的SQL性能比较低，就是做12次左连接，求出每个月的revenue。
代码如下：
# Write your MySQL query statement below select d0.id, Jan_Revenue,Feb_Revenue,Mar_Revenue,Apr_Revenue,May_Revenue,Jun_Revenue,Jul_Revenue, Aug_Revenue,Sep_Revenue,Oct_Revenue,Nov_Revenue,Dec_Revenue from (select distinct id from Department order by id ) d0 left join (select id,revenue as Jan_Revenue from Department where month = 'Jan') d1 on d0.id = d1.id left join (select id,revenue as Feb_Revenue from Department where month = 'Feb') d2 on d0.id = d2.id left join (select id,revenue as Mar_Revenue from Department where month = 'Mar') d3 on d0.id = d3.id left join (select id,revenue as Apr_Revenue from Department where month = 'Apr') d4 on d0.id = d4.id left join (select id,revenue as May_Revenue from Department where month = 'May') d5 on d0.id = d5.id left join (select id,revenue as Jun_Revenue from Department where month = 'Jun') d6 on d0.id = d6.id left join (select id,revenue as Jul_Revenue from Department where month = 'Jul') d7 on d0.id = d7.id left join (select id,revenue as Aug_Revenue from Department where month = 'Aug') d8 on d0.id = d8.id left join (select id,revenue as Sep_Revenue from Department where month = 'Sep') d9 on d0.id = d9.id left join (select id,revenue as Oct_Revenue from Department where month = 'Oct') d10 on d0.id = d10.id left join (select id,revenue as Nov_Revenue from Department where month = 'Nov') d11 on d0.id = d11.id left join (select id,revenue as Dec_Revenue from Department where month = 'Dec') d12 on d0.id = d12.id order by id;
