Several usages of Mysql case-when statement _mysql

String substitution processing using case

Copy Code code as follows:

Mysql> select * from sales;

9 rows in Set (0.01 sec)

SELECT name as name,
Case Category
When "Holiday" THEN "seasonal"
When "profession" THEN "bi_annual"
When ' literary ' THEN ' Random ' end as ' pattern '
From sales;

9 Rows in Set (0.00 sec)

CREATE TABLE Sales (
Num Mediumint not NULL auto_increment, name CHAR (m), winter int, spring int,
summer int, Fall int, category CHAR, primary key (NUM)
) Engine=myisam;

Insert into sales value (1, ' Java ', 1067, MB, 267, ' Holiday ');
Insert into Sales value (2, ' C ', 970,770,531,486, ' profession ');
Insert into Sales value (3, ' JavaScript ', 53,13,21,856, ' literary ');
Insert into Sales value (4, ' SQL ', 782,357,168,250, ' profession ');
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, ' profession ');

Simple Statement

Copy Code code as follows:

SELECT case when 10*2=30 THEN ' correct '
When 10*2=40 THEN ' correct '
ELSE ' Should be 10*2=20 '
End as ' result ';

Multi-expression

Copy Code code as follows:

SELECT Case 10*2
When THEN ' correct '
When THEN ' correct '
When THEN ' correct '
ELSE ' No results '
End as ' result ';

use case when in select query

Copy Code code as follows:

CREATE TABLE DVDs (
ID SMALLINT not NULL auto_increment PRIMARY KEY,
Name VARCHAR 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 requires a adult. '
When ' X ' THEN ' No one and under. '
When ' NR ' THEN ' with discretion when renting. '
ELSE ' OK to rent to minors. '
End as Policy
From DVDs
Order BY Name;

8 rows in Set (0.01 sec)