🎈 Write in front

🙋‍♂️ Hello everyone, I'm super dream. You can call me Xiaomeng~

It's time to practice SQL again! Let's study together!

🙋‍♂️ If you don't understand anything in the learning process, you are welcome to leave a message and ask questions in the comment area. Xiaomeng will tell you everything.

catalogue

🌌 SQL topic

🌌 Problem solving ideas

🌌 Method implementation

🌌 Code test

🌌 Summary of knowledge points

🌌 Previous recommendation

🌌 SQL topic

🌀 Department table:

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

Title:

Write an SQL query to reformat the table so that the new table has a department id column and some revenue columns corresponding to each month.

The query result format is shown in the following example:

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

Query 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 results table has 13 columns (1 Departments id column + 12 Revenue column for last month). 

 

🌌 Problem solving ideas

The department table stores the monthly income of all the people. The requirement here is to split the month column of the department into specific months.

   1. First, group the department s by id.
   2. Use case month when 'Jan' then revenue end to calculate the revenue in January (you can also use if(month = 'Jan', revenue, null)). It was written in this way for 12 months.

🌌 Method implementation

According to the above problem-solving ideas, we have two ways to solve it

Method 1:

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

Method 2:

select id,
	sum(if(month = 'Jan', revenue, null)) as 'Jan_Revenue',
	sum(if(month = 'Feb', revenue, null)) as 'Feb_Revenue',
	sum(if(month = 'Mar', revenue, null)) as 'Mar_Revenue',
	sum(if(month = 'Apr', revenue, null)) as 'Apr_Revenue',
	sum(if(month = 'May', revenue, null)) as 'May_Revenue',
	sum(if(month = 'Jun', revenue, null)) as 'Jun_Revenue',
	sum(if(month = 'Jul', revenue, null)) as 'Jul_Revenue',
	sum(if(month = 'Aug', revenue, null)) as 'Aug_Revenue',
	sum(if(month = 'Sep', revenue, null)) as 'Sep_Revenue',
	sum(if(month = 'Oct', revenue, null)) as 'Oct_Revenue',
	sum(if(month = 'Nov', revenue, null)) as 'Nov_Revenue',
	sum(if(month = 'Dec', revenue, null)) as 'Dec_Revenue'
from department group by id;

 

🌌 Code test

Consistent with the expected results, the test is successful! Execution time 160s

Consistent with the expected results, the test is successful! Execution time 196s

 

🌌 Summary of knowledge points

🌀case when:

Simple function (enumerate all possible values of this field)
    CASE [col_name] WHEN [value1] THEN [result1]...ELSE [default] END

Search function (the search function can write judgment, and the search function will only return the first qualified value, and other case s will be ignored)

CASE WHEN [expr] THEN [result1]...ELSE [default] END

🌀 if() function:

The IF function returns the first value or the second value according to whether the result of the condition is true or false.

IF(condition, value_if_true, value_if_false)

🌌 Previous recommendation

🚀 [leetcode SQL daily practice] - 627 Change gender

🚀 [leetcode SQL daily practice] - 620 Interesting movie

🚀 [leetcode SQL daily practice] - 196 Delete duplicate email