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

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!