MySQL Case statement (with instance)

This period of time, do the project to do tired, finally a bit of personal study time, use this hour, summed up, the most recent time to do statistics when the case statement it.

Structure: Case when ... then ... end

1. Change the value of the judgment at the same time

eg

The first type of notation

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 type of notation

SELECT Callernumber, case IsLocal

when 0 then ' outside line '

when 1 then ' inside ' END

from Cdr

2. Split one behavior multiple columns

eg

SELECT Sipaccount, COUNT (1) as Number,ischeck
From CDR
GROUP by Sipaccount,ischeck 

Split for this statistic (0 indicates no score, 1 is excellent, 2 is qualified, 3 is unqualified)

The final result is as follows:

So it ends up with a row split into three columns, with the following statement

SELECT Sipaccount,
(Case Ischeck If 1 then number END) Youxiu,
(Case Ischeck If 2 then number END) Hege,
(Case Ischeck If 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 this, and you'll find that although it's broken into three columns, the end result is not what you need, and then you need to group the results and process them according to Sipaccount. The statements are 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 If 1 then number END) Youxiu,
(Case Ischeck If 2 then number END) Hege,
(Case Ischeck If 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 result was finally obtained. Formally the format we need

MySQL Case statement (with instance)