SQL Server case

The case has two formats: the simple and the search function

Simple Case function

Example: Case Sex
When ' 1 ' Then ' men '
When ' 2 ' then ' women '
Else ' other ' End

Case Search function

Example: Case

When sex = ' 1 ' Then ' man '
When sex = ' 2 ' Then ' women '
Else ' other ' END

-There is also a problem to be aware that the case function returns only the first qualifying value, and the remaining case section is automatically ignored

Case when improved

In conjunction with the example understanding:

A

Requirement: The sum of the population of each continent according to the table?

SELECT SUM(population) asPopul,
CaseCountry
when ' China ' Then ' Asia '
when ' India ' Then ' Asia '
when ' Japan ' Then ' Asia '
when ' America ' Then ' North America '
when ' Canada ' Then ' North America '
when ' Mexico‘ Then ' North America '
ELSE ' Other ' ENDAs Area
fromCp
GROUP byArea

The results are as follows:

Two

Requirements: grouped by country and gender?

SelectCountry, sum ( case is sex=' 1 ' then population else 0 end) andsum (case when the sex=' 2 ' then population else 0 end) from the CSPGROUP BY C15>country;
The results are as follows:

(iii) Select Case when usage

Result 1:   result 2:

Example 1

SELECT
COUNT ( case if sex = 1 then 1
ELSE NULL
END) number of boys,
COUNT ( case if sex = 2 then 1
ELSE NULL
END) number of girls
from students GROUP by grade;

Example 2

select    
  COUNT (CASE  when sex =  1 THEN 1&NBSP
              ELSE NULL
      &NBS P       end )   number of boys,
  COUNT (CASE  when sex  =  2 then 1
              ELSE NULL
      &N Bsp       end )   number of females
  From students  

(iv)where case when usage

Result 1 result 2

Example 1

select Span style= "COLOR: #000000" >*
  from students a
  WHERE (case
           when a. SEX=&NBSP 1 then 1
         else 0

END) = 1 | 0

(v) group by case when usage

Results

select
    case
       when sex = 1 then ' 1 '
       else NULL
    END Sex,
    count (*) Num
from students

GROUP by
Case
When sex =1 then ' 1 '
ELSE NULL
END

SQL Server case