Home >Database >Mysql Tutorial >How to use mysql case statement?
MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently a product of Oracle.
So how to use the mysql case statement?
mysql case statement usage:
Structure: case when… then…end
1. Change its value while judging
eg:
select OperatorAccount, case when CreateTime>'2016-02-14 16:24:42' then 'after' when CreateTime<'2016-02-14 16:24:42' then 'before' else 'now' end stage from log_login order by CreateTime DESC
The second way of writing
SELECT CallerNumber, CASE IsLocal WHEN 0 THEN '外线' WHEN 1 THEN '内线' END FROM cdr
2. Split one row into multiple columns
eg:
SELECT SipAccount, COUNT(1) AS number,IsCheck FROM cdr GROUP BY SipAccount,IsCheck
Split this statistical result (0 means not scored, 1 means excellent, 2 means qualified, 3 means unqualified)
The final results are as follows :
So in the end, we need to split the row into three columns. The statement is as follows
SELECT SipAccount, (CASE IsCheck WHEN 1 THEN number END) youxiu, (CASE IsCheck WHEN 2 THEN number END) hege, (CASE IsCheck WHEN 3 THEN number END) buhege FROM (SELECT SipAccount, COUNT(1) AS number,IsCheck FROM cdr GROUP BY SipAccount,IsCheck) AS a
Now the result is like this Yes, you will find that although it is split into three columns, the final result is not what you need. Next, you need to group the results according to sipaccount and process the results at the same time. The statement is as follows:
SELECT sipaccount, IF(MAX(youxiu) IS NULL,0, MAX(youxiu)) youxiu, IF(MAX(hege) IS NULL,0, MAX(hege)) hege, IF(MAX(buhege) IS NULL,0, MAX(buhege)) buhege FROM (SELECT SipAccount, (CASE IsCheck WHEN 1 THEN number END) youxiu, (CASE IsCheck WHEN 2 THEN number END) hege, (CASE IsCheck WHEN 3 THEN number END) buhege FROM (SELECT SipAccount, COUNT(1) AS number,IsCheck FROM cdr GROUP BY SipAccount,IsCheck) AS a) AS b GROUP BY sipaccount##Finally got this result. Exactly the format we need.
The above is the detailed content of How to use mysql case statement?. For more information, please follow other related articles on the PHP Chinese website!