Creation of tables
CREATE TABLE ' Lee ' (
' id ' int (ten) is not NULL auto_increment,
' Name ' char (DEFAULT NULL),
' Birthday ' datetime DEFAULT NULL,
PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8
Data insertion:
Insert into Lee (Name,birthday) VALUES (' Sam ', ' 1990-01-01 ');
Insert into Lee (Name,birthday) VALUES (' Lee ', ' 1980-01-01 ');
Insert into Lee (Name,birthday) VALUES (' John ', ' 1985-01-01 ');
The first usage:
SELECT name,
case where Birthday < ' 1981 ' then ' old '
When Birthday > ' 1988 ' Then ' Yong '
ELSE ' OK ' END Yorn
From Lee
Second usage:
SELECT name, case name
When the ' Sam ' Then ' Yong '
When the ' Lee ' then ' Handsome '
ELSE ' good ' END as Oldname
From Lee
The third: Of course, the case when statement can also compound
Select Name, Birthday,
Case
When Birthday > ' 1983 ' Then ' Yong '
When Name= ' Lee ' then ' Handsome '
Else ' Just so ' end
from Lee;
In this case the date comparison with the SQL statement, you need to quote the year, or else the result may be different from the expected result,
Of course, you can also use the year function to implement
Select Name,
Case if 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) is not NULL,
Primary KEY (Paymentno)
)
Insert into penalties values (1, ' 2008-01-01 ', 3.45);
Insert into penalties values (2, ' 2009-01-01 ', 50.45);
Insert into penalties values (3, ' 2008-07-01 ', 80.45);
First question: The fine registration is divided into three categories, the first class of low, including a fine greater than 0 is less than or equal to 40, the second class moderate greater than 40 to 80 fine, the third class high contains all fines greater than 80
Select Payment_date, Amount,
Case
When amount >= 0 and amount < "low"
When amount >=40 and amount < ' moderate '
When amount >=80 then ' high '
Else ' null ' END
From penalties
The second question: Statistics The penalty number belonging to low
SELECT * FROM
(select Paymentno, Amount,
Case
When amount >= 0 and amount < "low"
When amount >=40 and amount < ' moderate '
When amount >=80 then ' high '
Else ' incorrect ' end LVL
From penalties) as P
where p.lvl = ' low '
MySQL Case then uses