Case-when statement in SQL

Source: Internet
Author: User

A table data is as follows
1900-1-1 Wins
1900-1-1 Wins
1900-1-1 Negative
1900-1-2 Wins
1900-1-2 win
write out a sql win   negative
1900-1 -1 2   1
1900-1 -2 2   0

I have built a table like this:

Test(Datevarchar()null,Resultvarchar()null )

and insert the above data into the table.

After some attempts and modifications, we finally get the answer:

SelectDistinctDate,Sum(Case Result When Sheng Then 1 Else 0 End) As  ' win ' ,sum ( case result when  ' negative '  then 1  else 0end)  as ' negative ' from test group by date    

What I'm going to say here is, in fact, the use of case in SQL. It does not seem to be common in normal SQL statements, and I have not used them in actual projects before. Similar problems are encountered, often implemented through code or multiple SQL statements. or the ugly SQL below, with a lot of potential bugs (e.g., when there's no ' negative ').

Select A.Date,A.A1Wins,B.B1Negative From (Select Date,Count(Date) A1From Testwhere Result =Sheng Group By Date) A, (Select date,count ( date)  b1 from  test where result = ' negative '  group  by date)  b where a date=b. Date                  

We might as well review the syntax of case.

Case when there are two uses, one is similar to the example in the above simple cases function:

Result    ' wins '1' negative '20END     

There is also a case search function:

Result=' win '1result=' Negative '20END       

Where result= ' wins ' can be replaced by other conditional expressions. If more than one case-when expression meets the criteria, only the first qualifying clause is returned, and the remaining clauses are ignored.

Using case-When statements can simplify many of the problems that we encounter in our usual work. If the gender in the table is the number 1, 2, but want to query out male, female, you can:

Select  (case gender  when 1then  ' men '  when  2 then ' female '  else  ' other '  end)  as  gender fromtable1               

Isn't it strong?

Case-when statement in SQL

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.