MySQL statement case when

Table Creation

 

Create Table 'lil '(
'Id' int (10) Not null auto_increment,
'Name' char (20) default null,
'Birthday' datetime default null,
Primary Key ('id') engine = InnoDB default charset = utf8

 

Data insertion:

Insert into Lee (name, birthday) values ('Sam ', '2017-01-01 ');

Insert into Lee (name, birthday) values ('Lee ', '2017-01-01 ');

Insert into Lee (name, birthday) values ('john', '2017-01-01 ');

 

Use Case when statement

1.

Select name,
Case
When birthday <'000000' then 'old'
When birthday> '123' then 'yong'
Else 'OK' end Yorn
From Lee;

 

 

2.

Select name,
Case name
When 'Sam 'then 'yong'
When 'Lee 'then' hands'
Else 'good' end
From Lee;

 

Of course, the case when statement can be combined.

3.

Select name, birthday,
Case
When birthday> '123' then 'yong'
When name = 'Lee 'then' handsome'
Else 'Just so so' end
From Lee;

If you use an SQL statement to compare dates, you need to quote the year in quotation marks. Otherwise, the results may be different from the expected results. My MySQL version 5.1

Of course, you can also use the year function. Take the first SQL statement as an example.

Select name,
Case
When year (birthday)> 1988 then 'yong'
When year (birthday) <1980 then 'old'
Else 'OK' end
From Lee;

 

Create Table penalties
(
Paymentno integer not null,
Payment_date date not null,
Amount decimal (7,2) not null,
Primary Key (paymentno)
)

Insert into penalties values (1, '2017-01-01 ', 2008 );
Insert into penalties values (2, '2017-01-01 ', 2009 );
Insert into penalties values (3, '2017-07-01 ', 2008 );

1. # There are three types of fine registration: the first category of low, including fines greater than 0 and less than or equal to 40, and the second category of moderate is greater than 40
# Fines between 80 and 80. The third category of high includes all fines greater than 80.

2. # calculate the number of low fines.

 

The solution of the first question is the same as the above
Select paymentno, amount,
Case
When amount> 0 and amount <= 40 then 'low'
When amount> 40 and amount <= 80 then 'moderate'
When amount> 80 then 'high'
Else 'encrect 'End LVL
From 'penalties'

2. # calculate the number of low fines. Focus on the solution here
Method 1.
Select paymentno, amount
From 'penalties'
Where case
When amount> 0 and amount <= 40 then 'low'
When amount> 40 and amount <= 80 then 'moderate'
When amount> 80 then 'high'
Else 'encrect 'end = 'low ';

Method 2
Select *
From (select paymentno, amount,
Case
When amount> 0 and amount <= 40 then 'low'
When amount> 40 and amount <= 80 then 'moderate'
When amount> 80 then 'high'
Else 'encrect 'End LVL
From 'penalties ') as P
Where p. LVL = 'low ';