MySQL Case statement (with instance)

Source: Internet
Author: User
Tags case statement

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.