当前位置：网站首页>Database daily question --- day 24: format department table

Database daily question --- day 24: format department table

2022-07-18 16:33:00 【InfoQ】

One 、 Problem description

Departmental table

Department

：

+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+---------------+---------+
(id, month)  Is the union primary key of the table .
 This form has information about the monthly income of each department .
 month （month） You can take the following values  [&quot;Jan&quot;,&quot;Feb&quot;,&quot;Mar&quot;,&quot;Apr&quot;,&quot;May&quot;,&quot;Jun&quot;,&quot;Jul&quot;,&quot;Aug&quot;,&quot;Sep&quot;,&quot;Oct&quot;,&quot;Nov&quot;,&quot;Dec&quot;].

Write a

SQL

Query to reformat the table , Make the new table have a department id Columns and some correspondences  

Every month

The income of the

（revenue）

Column .

Query results

The format is shown in the following example ：

Topic link

：

Format department table

Two 、 Subject requirements

Examples

Department  surface ：
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+

 The result table obtained by query ：
+------+-------------+-------------+-------------+-----+-------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1 | 8000 | 7000 | 6000 | ... | null |
| 2 | 9000 | null | null | ... | null |
| 3 | null | 10000 | null | ... | null |
+------+-------------+-------------+-------------+-----+-------------+

 Be careful , The result table has  13  Column  (1 Departments  id  Column  + 12 The revenue column for the last month ).

Investigate

1. Aggregate functions 、case sentence 
2. It is recommended to use time 10~25min

3、 ... and 、 Problem analysis

The title requires us to find out the number of each user in

12

A month's salary , Those without salary are set as

null

, Note that each line in the title contains repeated numbers , The output results include

id

Should have

Column .

We can use

case  Variable  when  Conditions  then  result 1 else  result 2

Such code comes to handle , For the overall monthly salary , First pair

id

Number to aggregate groups , And then use

sum

Function to calculate the sum of monthly salary .

Four 、 coded

select id,
 sum(case month when 'Jan' then revenue else null end) as Jan_Revenue,
 sum(case month when 'Feb' then revenue else null end) as Feb_Revenue,
 sum(case month when 'Mar' then revenue else null end) as Mar_Revenue,
 sum(case month when 'Apr' then revenue else null end) as Apr_Revenue,
 sum(case month when 'May' then revenue else null end) as May_Revenue,
 sum(case month when 'Jun' then revenue else null end) as Jun_Revenue,
 sum(case month when 'Jul' then revenue else null end) as Jul_Revenue,
 sum(case month when 'Aug' then revenue else null end) as Aug_Revenue,
 sum(case month when 'Sep' then revenue else null end) as Sep_Revenue,
 sum(case month when 'Oct' then revenue else null end) as Oct_Revenue,
 sum(case month when 'Nov' then revenue else null end) as Nov_Revenue,
 sum(case month when 'Dec' then revenue else null end) as Dec_Revenue
from Department
group by id