“This is the 12th day of my participation in the Gwen Challenge in November. See details of the event: The Last Gwen Challenge 2021”.

Tips: This article is about 1732 words, read about 2-3 minutes, hope you can read patiently, if you are already familiar with the knowledge points, you can directly through the directory to jump to place of interest to you, hope you read this article can help you, if you have any good Suggestions and views in the process of reading, welcome to leave a message at the bottom of the article or direct messages, I if sleep of the article Chapter to bring you a little help, you can help click the like and attention, thank you!!

preface

Function: you can use them to determine the function in the database, and the code in the if… Else does the same thing. However, there are differences between them. Here are their specific roles and differences.

One: Use grammar

(1) Ordinary case function

CASE < expression > WHEN < value1> THEN < operation > WHEN < value2> THEN < operation >... ELSE < operation > ENDCopy the code

(1) Search case function

CASE WHEN < condition1> THEN < command > WHEN < condition2> THEN < command >... ELSE commands ENDCopy the code

Function 1: Combine grouped statistics

Demand: Figure out the total number of people by “continent”

(I) Method 1: Use ordinary case function for statistics

		select (
		case name 
			when 'China' then 'Asia'
			when 'Japan' then 'Asia'
			when 'the United States' then 'North America'
			when 'Canada' then 'North America'
		else 'other'End, sum(populationfrom t_country
		GROUP BY
		(
		case name 
			when 'China' then 'Asia'
			when 'Japan' then 'Asia'
			when 'the United States' then 'North America'
			when 'Canada' then 'North America'
		else 'other' end
		)
Copy the code

Method 1 statistical results

(2) Method 2: Use the case function of search for statistics

	select (
		case  
		when name in('China'.'Japan')Then 'Asia' when namein('the United States'.'Canada')Then 'North America'else'Other' end) continent,sum(population)Total number from T_country GROUPBY
	(
		case  
		when name in('China'.'Japan')Then 'Asia' when namein('the United States'.'Canada')Then 'North America'else'Other' end)Copy the code

Method two: Statistical results



Function 2: Update field values by condition

(I) Demand: Increase the salary of employees with salary below 3000 by 20%, and the salary of employees with salary above 3000 by 8%. The data are as follows:

The first thing you might want to do is update it directly with the following two update statements:

update t_salary set salary = salary + (salary * 0.2) where salary < 3000;
update t_salary set salary = salary + (salary * 0.08) where salary >= 3000;
Copy the code

But, if this is it, in fact, there will be problems, such as: the original salary employees in 2900, after the first statement after the salary will become 3480, and at this point, and then execute the second update statement, because meet wage is more than three thousand, and will go to add more than 8% of the wages, this is obviously doesn’t meet our requirements, so, if you want to complete This requirement, without writing too complex SQL, can be accomplished through the case function.

(2) Use the search case function to modify the conditions (simple case function cannot be used here, because simple case function cannot judge the conditions with the range)

update t_salary
	set 
	salary = 
	(
		case 
		 when salary < 3000 then salary + salary * 0.2
		 when salary >= 3000 then salary + salary * 0.08
		 else salary 
		end
	)
Copy the code

(3) the results after the sub-conditions are modified



Function 3: Check whether field values in the table are consistent

(1) Requirement: determine whether the value of the name field in the two tables is consistent and return the result as follows:

(2) Use the search case function to modify the conditions (simple case function cannot be used here, because simple case function cannot judge the conditions with the range)

select name,
(
	case 
	when desciption in(select description from t_user2)Then 'be consistent'else'inconsistent' end) from t_user1Copy the code

   (III) Comparison Results:


Function 4: Line to column (key – common interview)

(I) Requirements: Sort the data in the table according to the name, subject and score of each student as follows:

(2) use case function conversion

// Use the normal case function
SELECT NAME,
	max( CASE class WHEN'Chinese'THEN grade ELSE 0 END) Chinese,max( CASE class WHEN'mathematics'THEN grade ELSE 0 END) mathematics,max( CASE class WHEN'English'THEN grade ELSE 0 END) EnglishFROM
	t_source 
GROUP BY
NAME// Use searchcasefunctionSELECT NAME.max( CASE  WHEN class = 'Chinese' THEN grade ELSE 0END) Language, Max (CASE WHENclass =  'mathematics' THEN grade ELSE 0END) math, Max (CASE WHENclass = 'English' THEN grade ELSE 0FROM t_source GROUP BY NAMECopy the code

(iii) Conversion results



Five: ordinary case function and search case function difference

As can be seen from the above cases, the ordinary case function is relatively simple in writing, but also relatively simple in function. The function of searching case function is more powerful, as follows:

1. A simple case function can search for subqueries such as In, greater than or equal to, etc.

2. If you just use simple grouping of conditions, you can choose the plain case function. If you need to judge more scenarios, you can choose the search case.

Six: summarize

If you want to practice, need this article test data, can reply: [test data]

Here, you should have a deeper understanding of the Case function, but the use of Case function is far more than this article describes, we need to find more possibilities in practice, if you have any questions or errors in this article, welcome to private message or comment below to point out.

Code word is not easy, if you think this article is a little help to you, you can like and follow!