How to use mysql database case statements

Table A: See the following figure.

Table B: As shown in the following figure:

 

 

To output the order number of status = 6 under master_id, the output result is as follows:

 

The statement adopted at the beginning is: select count (. order_id) from a left join B on. order_id = B. order_id where B. status = 6 group by. master_id. It is found that when status = 6 does not exist, the query result is null, and it does not return 0 as imagined.

Use the case statement to solve this problem:

The code is as follows:	Copy code
Select. master_id, sum (case when B. status = 6 then 1 else 0 end) as total_count from a inner join B on. order_id = B. id group by. master_id.

When the case statement is executed, 1 is returned if status = 6 is met. Otherwise, 0 is returned. sum is used to calculate the number of qualified conditions.

Now let's sort out some simple examples for your reference.

The code is as follows:

Copy code

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 '); First usage: SELECT name, Case when birthday <'000000' THEN 'old' WHEN birthday> '123' THEN 'Yong' ELSE 'OK' END YORN FROM lee Second usage: Select name, CASE name WHEN 'Sam 'Then 'Yong' WHEN 'Lee 'then' hands' ELSE 'good' END as oldname FROM lee Third: of course, the case when Statement can be composite. 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. Otherwise, the results may be different from the expected results, Of course, you can also use the year function to implement 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 ); Question 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 fines between 40 and 80, category 3 high includes all fines greater than 80 Select payment_date, amount, Case When amount> = 0 AND amount <40 then 'low' When amount> = 40 AND amount <80 then 'moderate' When amount> = 80 then 'high' Else 'null' END FROM penalties Question 2: calculate the number of low fines 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'