Case-when statement in SQL

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