How to use the CASEWHEN statement of MySQL _ MySQL

MySQL CASEWHEN statement usage instructions bitsCN.com

The case when Statement in the mysql database.

The case when statement is used to calculate the condition list and return one of multiple possible result expressions.

CASE has two formats:

The simple CASE function compares an expression with a group of simple expressions to determine the result.

The CASE search function calculates a group of Boolean expressions to determine the result.
Both formats support the optional ELSE parameter.

Syntax
Simple CASE functions:


CASE input_expression
WHEN when_expression THEN result_expression
[... N]
[
ELSE else_result_expression
END

CASE Search function:


CASE
WHEN Boolean_expression THEN result_expression
[... N]
[
ELSE else_result_expression
END

Parameters
Input_expression

Is the expression calculated when the simple CASE format is used. Input_expression is any valid Microsoft? SQL Server? Expression.

WHEN when_expression

Simple expressions compared with input_expression in simple CASE format. When_expression is any valid SQL Server expression. The data type of Input_expression and when_expression must be the same or implicit conversion.

Placeholder, indicating that multiple WHEN when_expression THEN result_expression clauses or WHEN Boolean_expression THEN result_expression clauses can be used.

THEN result_expression

The expression returned when input_expression = when_expression is set to TRUE or Boolean_expression is set to TRUE.
Result expression is any valid SQL Server expression.

ELSE else_result_expression

The expression returned when the value of the comparison operation is not TRUE. If this parameter is omitted and the value of the comparison operation is not TRUE, CASE returns NULL. Else_result_expression is any valid SQL Server expression. Else_result_expression and all result_expressions must have the same data type or be implicitly converted.

WHEN Boolean_expression

The Boolean expression calculated when the CASE search format is used. Boolean_expression is any valid Boolean expression.

Result type

Returns the highest priority rule type from the type set of result_expressions and the optional else_result_expression. For more information, see data type precedence.

Result value

Simple CASE functions:
Calculate input_expression, and then calculate the input_expression = when_expression of each WHEN clause in the specified order.

Returns the result_expression of the first (input_expression = when_expression) value that is TRUE.

If no input_expression = when_expression is set to TRUE, SQL Server returns else_result_expression when the ELSE clause is specified. If no ELSE clause is specified, NULL is returned.
CASE Search function:
Evaluate the Boolean_expression of each WHEN clause in the specified order.

Returns the result_expression of the first Boolean_expression with the value TRUE.

If no Boolean_expression is set to TRUE, SQL Server returns else_result_expression when the ELSE clause is specified. If no ELSE clause is specified, NULL is returned.

The following are examples of mysql case when statements.

A. Use a select statement with A simple CASE function
In the SELECT statement, the simple CASE function only checks for equality without performing other comparisons.

Example: use the CASE function to change the classification display of books.

USE pubs
GO
SELECT Category =
CASE type
WHEN 'popular _ comp 'then' popular Computing'
WHEN 'mod _ cook' THEN 'Modern cooking'
WHEN 'business' THEN 'business'
WHEN 'sychology 'Then 'Psychology'
WHEN 'Trad _ cook' THEN 'traditional cooking'
ELSE 'not yet categorized'
END,
CAST (title AS varchar (25) AS 'shortened title ',
Price AS Price
FROM titles
WHERE price IS NOT NULL
Order by type, price
Compute avg (price) BY type
GO

Note. then I tried not to use category =.

The code I used is:

SELECT
Case gender
WHEN 1 THEN 'Nan'
WHEN 0 THEN 'nv'
End as gender
FROM
T_swidy_day_nutrient

Result set:

Category Shortened Title Price
----------------------------------------------------------------------
Business You Can Combat Computer S 2.99
Business Cooking with Computers: S 11.95
Business The Busy Executive's Data 19.99
Business Straight Talk About Compu 19.99

Avg
======================================
13.73

Category Shortened Title Price
----------------------------------------------------------------------
Modern Cooking The Gourmet Microwave 2.99
Modern Cooking Silicon Valley Gastronomi 19.99

Avg
======================================
11.49

Category Shortened Title Price
----------------------------------------------------------------------
Popular Computing Secrets of Silicon Valley 20.00
Popular Computing But Is It User Friendly? 22.95

Avg
======================================
21.48

Category Shortened Title Price
----------------------------------------------------------------------
Psychology Life Without Fear 7.00
Psychology Emotional Security: A New 7.99
Psychology Is Anger the Enemy? 10.95
Psychology Prolonged Data Deprivatio 19.99
Psychology Computer Phobic AND Non-P 21.59

Avg
======================================
13.50

Category Shortened Title Price
----------------------------------------------------------------------
Traditional Cooking interval Ty Years in Buckheim 11.95
Traditional Cooking Sushi, Anyone? 14.99
Traditional Cooking Onions, Leeks, and Garlic 20.95

Avg
======================================
15.96

(21 row (s) affected)

B. use functions with simple CASE functions and CASE search functions

SELECT statement
In the SELECT statement, the CASE search function allows you to replace the value in the result set based on the comparison value.

Example: display the price (money column) as a text comment based on the price range of the book.

USE pubs
GO
SELECT 'price Category '=
CASE
WHEN price is null then 'Not yet priced'
WHEN price <10 THEN 'very Reasonable title'
WHEN price> = 10 and price <20 THEN 'coffee Table title'
ELSE 'expensive book! '
END,
CAST (title AS varchar (20) AS 'shortened title'
FROM titles
Order by price
GO

Result set:

Price Category Shortened Title
-----------------------------------------
Not yet priced Net Etiquette
Not yet priced The Psychology of Co
Very Reasonable Title The Gourmet Microwav
Very Reasonable Title You Can Combat Compu
Very Reasonable Title Life Without Fear
Very Reasonable Title Emotional Security:
Coffee Table Title Is Anger the Enemy?
Coffee Table Title Cooking with Compute
Coffee Table Title policty Years in Bucki
Coffee Table Title Sushi, Anyone?
Coffee Table Title Prolonged Data Depri
Coffee Table Title Silicon Valley Gastr
Coffee Table Title Straight Talk About
Coffee Table Title The Busy Executive's
Expensive book! Secrets of Silicon V
Expensive book! Onions, Leeks, and G
Expensive book! Computer Phobic And
Expensive book! But Is It User Frien

(18 row (s) affected)

C. use the CASE function with SUBSTRING and SELECT

Example: use CASE and THEN to generate a list of authors, book identification numbers, and book types of each author.

USE pubs
Select substring (RTRIM (a. au_fname) + ''+
RTRIM (a. au_lname) + ''), 1, 25) AS Name, a. au_id, ta. title_id,
Type =
CASE
When substring (ta. title_id, 1, 2) = 'bus' THEN 'business'
When substring (ta. title_id, 1, 2) = 'MC 'then' Modern cooking'
When substring (ta. title_id, 1, 2) = 'PC' then' Popular Computing'
When substring (ta. title_id, 1, 2) = 'Ps' THEN 'Psychology'
When substring (ta. title_id, 1, 2) = 'TC 'then' Traditional Cooking'
END
FROM titleauthor ta JOIN authors a ON ta. au_id = a. au_id

Result set:

Name au_id title_id Type
---------------------------------------------------------------
Johnson White 172-32-1176 PS3333 Psychology
Marjorie Green 213-46-8915 BU1032 Business
Marjorie Green 213-46-8915 BU2075 Business
Cheryl Carson 238-95-7766 PC1035 Popular Computing
Michael O 'Leary 267-41-2394 BU1111 Business
Michael O 'Leary 267-41-2394 TC7777 Traditional Cooking
Dean Straight 274-80-9391 BU7832 Business
Abraham Bennet 409-56-7008 BU1032 Business
Ann Dull 427-17-2319 PC8888 Popular Computing
Burt Gringlesby 472-27-2349 TC7777 Traditional Cooking
Charlene Locksley 486-29-1786 PC9999 Popular Computing
Charlene Locksley 486-29-1786 PS7777 Psychology
Reginald Blotchet-Hils 648-92-1872 TC4203 Traditional Cooking
Akiko Yokomoto 672-71-3249 TC7777 Traditional Cooking
Innes del Castillo 712-45-1867 MC2222 Modern Cooking
Micel DeFrance 722-51-5454 MC3021 Modern Cooking
Stearns MacFeather 724-80-9391 BU1111 Business
Stearns MacFeather 724-80-9391 PS1372 Psychology
Livia Karsen 756-30-7391 PS1372 Psychology
Sylvia Panteley 807-91-6654 TC3218 Traditional Cooking
Sheryl Hunter 846-92-7186 PC8888 Popular Computing
Anne Ringer 899-46-2035 MC3021 Modern Cooking
Anne Ringer 899-46-2035 PS2091 Psychology
Albert Ringer 998-72-3567 PS2091 Psychology
Albert Ringer 998-72-3567 PS2106 Psychology
(25 row (s) affected)

CASE may be one of the keywords most misused in SQL.
Although this keyword may have been used before to create a field, it also has more features.

For example, you can use CASE in the WHERE clause.

First, let's look at the CASE syntax. In a general SELECT statement, the syntax is as follows:


SELECT =
CASE
WHEN THEN
WHEN THEN
ELSE
END

The above code should replace the content in angle brackets with specific parameters.

Example:

USE pubs
GO
SELECT
Title,
'Price Range' =
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'gift to impress relatives'
END
FROM titles
Order by price
GO

This is a typical example of CASE, but you can do more with CASE.

For example, the CASE in the group by clause below:


SELECT 'number of tidles ', Count (*)
FROM titles
GROUP
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'gift to impress relatives'
END
GO

You can even combine these options to add an order by clause, for example:

USE pubs
GO
SELECT
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'gift to impress relatives'
End as Range,
Title
FROM titles
GROUP
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'gift to impress relatives'
END,
Title
ORDER
CASE
WHEN price is null then 'unpriced'
WHEN price <10 THEN 'bargain'
WHEN price BETWEEN 10 and 20 THEN 'average'
ELSE 'gift to impress relatives'
END,
Title
GO

Note: To use CASE in the group by block, the query statement must repeat the CASE block in the SELECT block in the group by block.

In addition to selecting custom fields, CASE is useful in many cases.

A little deeper, you can also get the grouping sorting result set that was previously considered impossible.

Use case when for string replacement

/*
Mysql> select * from sales;
+ ----- + ------------ + -------- + ------ + ------------ +
| Num | name | winter | spring | summer | fall | category |
+ ----- + ------------ + -------- + ------ + ------------ +
| 1 | Java | 1067 | 200 | 150 | 267 | Holiday |
| 2 | C | 970 | 770 | 531 | 486 | Sion |
| 3 | JavaScript | 53 | 13 | 21 | 856 | Literary |
| 4 | SQL | 782 | 357 | 168 | 250 | Sion |
| 5 | Oracle | 589 | 795 | 367 | 284 | Holiday |
| 6 | MySQL | 953 | 582 | 336 | 489 | Literary |
| 7 | Cplus | 752 | 657 | 259 | 478 | Literary |
| 8 | Python | 67 | 23 | 83 | 543 | Holiday |
| 9 | PHP | 673 | 48 | 625 | 52 | Sion |
+ ----- + ------------ + -------- + ------ + ------------ +
9 rows in set (0.01 sec)
Mysql> SELECT name AS Name,
-> CASE category
-> WHEN "Holiday" THEN "Seasonal"
-> WHEN "Sion" THEN "Bi_annual"
-> WHEN "Literary" THEN "Random" end as "Pattern"
-> FROM sales;
+ ------------ + ----------- +
| Name | Pattern |
+ ------------ + ----------- +
| Java | Seasonal |
| C | Bi_annual |
| JavaScript | Random |
| SQL | Bi_annual |
| Oracle | Seasonal |
| MySQL | Random |
| Cplus | Random |
| Python | Seasonal |
| PHP | Bi_annual |
+ ------------ + ----------- +
9 rows in set (0.00 sec)
*/
Drop table sales;
Create table sales (
Num mediumint not null AUTO_INCREMENT,
Name CHAR (20 ),
Winter INT,
Spring INT,
Summer INT,
Fall INT,
Category CHAR (13 ),
Primary key (num)
) Type = MyISAM;
Insert into sales value (1, 'Java ', 1067,200,150,267, 'Holiday ');
Insert into sales value (2, 'C', 970,770,531,486, 'Sale sion ');
Insert into sales value (3, 'javascript ', 21,856, 'litery ');
Insert into sales value (4, 'SQL', 782,357,168,250, 'analysion ');
Insert into sales value (5, 'Oracle ', 589,795,367,284, 'Holiday ');
Insert into sales value (6, 'mysql', 953,582,336,489, 'literary ');
Insert into sales value (7, 'cplus ', 752,657,259,478, 'literary ');
Insert into sales value (8, 'Python', 67,23, 83,543, 'Holiday ');
Insert into sales value (9, 'php', 673,48, 625,52, 'plusion ');
Select * from sales;
SELECT name AS Name,
CASE category
WHEN "Holiday" THEN "Seasonal"
WHEN "Sion" THEN "Bi_annual"
WHEN "Literary" THEN "Random" end as "Pattern"
FROM sales;

Simple statement

Select case when 10*2 = 30 THEN '30 correct'
WHEN 10*2 = 40 THEN '40 correct'
ELSE 'should be 10*2 = 20'
END;

Multiple expressions

Select case 10*2
WHEN 20 THEN '20 correct'
WHEN 30 THEN '30 correct'
WHEN 40 THEN '40 correct'
END;

Use case when in SELECT query

/*
Mysql> SELECT Name, RatingID AS Rating,
-> CASE RatingID
-> WHEN 'r'then' Under 17 requires an adult .'
-> WHEN 'X' No one 17 and under .'
-> WHEN 'NR 'then' Use discretion when refreshing .'
-> ELSE 'OK to rent to minors .'
-> End as Policy
-> FROM DVDs
-> Order by Name;
+ ----------- + -------- + ------------------------------ +
| Name | Rating | Policy |
+ ----------- + -------- + ------------------------------ +
| Africa | PG | OK to rent to minors. |
| Amadeus | PG | OK to rent to minors. |
| Christmas | NR | Use discretion when refreshing. |
| Doc | G | OK to rent to minors. |
| Falcon | NR | Use discretion when authentication ing. |
| Mash | R | Under 17 requires an adult. |
| Show | NR | Use discretion when refreshing. |
| View | NR | Use discretion when refreshing. |
+ ----------- + -------- + ------------------------------ +
8 rows in set (0.01 sec)
*/
Drop table DVDs;
Create table DVDs (
Id smallint not null AUTO_INCREMENT primary key,
Name VARCHAR (60) not null,
NumDisks tinyint not null default 1,
RatingID VARCHAR (4) not null,
StatID CHAR (3) NOT NULL
)
ENGINE = INNODB;
Insert into DVDs (Name, NumDisks, RatingID, StatID)
VALUES ('Christmas ', 1, 'nr', 'S1 '),
('Doc', 1, 'G', 'S2 '),
('Africa ', 1, 'PG', 'S1 '),
('Falcon ', 1, 'nr', 'S2 '),
('Amadeus ', 1, 'PG', 'S2 '),
('Show', 2, 'nr ', 'S2 '),
('View', 1, 'nr ', 'S1 '),
('Mash ', 2, 'R', 'S2 ');
SELECT Name, RatingID AS Rating,
CASE RatingID
WHEN 'r'then' Under 17 requires an adult .'
WHEN 'X' No one 17 and under .'
WHEN 'NR 'then' Use discretion when processing Ing .'
ELSE 'OK to rent to minors .'
End as Policy
FROM DVDs
Order by Name;
BitsCN.com