How to use mysql case statement?-Mysql Tutorial-php.cn

MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently a product of Oracle.

How to use mysql case statement?

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

Copy after login

How to use mysql case statement?

The second way of writing

SELECT CallerNumber, CASE IsLocal WHEN 0 THEN '外线' WHEN 1 THEN '内线' END FROM cdr

Copy after login

How to use mysql case statement?

2. Split one row into multiple columns

eg:

SELECT SipAccount, COUNT(1) AS number,IsCheck FROM cdr GROUP BY SipAccount,IsCheck

Copy after login

How to use mysql case statement?

Split this statistical result (0 means not scored, 1 means excellent, 2 means qualified, 3 means unqualified)

The final results are as follows ：

How to use mysql case statement?

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

Copy after login

How to use mysql case statement?

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

Copy after login

How to use mysql case statement?

##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!