MySQL statement case whenmysql statement case when

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> '000000' then' Yong 'else' OK 'end Yorn from Lee;

 

 

2.

Select name, Case name when 'Sam 'then' Yong 'when' Lee 'then' handsome'else 'good' end from Lee;

 

Of course, the case when statement can be combined.

3.

Select name, birthday, case when birthday> '2013' then 'yong' when name = 'Lee 'then' handsome 'else' just 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', 3.45); 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 fines between moderate and 40 # 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 preceding 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' incorrect 'end LVL from 'penalties'

2. # calculate the number of low fines. Focus on the solution hereMethod 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' incorrect' 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' incorrect 'end LVL from 'penalties ') as P where p. LVL = 'low ';

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> '000000' then' Yong 'else' OK 'end Yorn from Lee;

 

 

2.

Select name, Case name when 'Sam 'then' Yong 'when' Lee 'then' handsome'else 'good' end from Lee;

 

Of course, the case when statement can be combined.

3.

Select name, birthday, case when birthday> '2013' then 'yong' when name = 'Lee 'then' handsome 'else' just 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', 3.45); 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 fines between moderate and 40 # 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 preceding 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' incorrect 'end LVL from 'penalties'

2. # calculate the number of low fines. Focus on the solution hereMethod 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' incorrect' 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' incorrect 'end LVL from 'penalties ') as P where p. LVL = 'low ';