Simple use of the when and then ... else end in MySQL

The data SQL CASE expression is a generic conditional expression that resembles a if/else statement in another language.

Case if condition then result

When condition then result

.............
[When ...]
[ELSE result]
END

The case clause can be used where any expression can exist effectively. Condition is an expression that returns a Boolean. If the result is true, then the result of the case expression is a qualified result. If the result is false, then search for any subsequent when clauses in the same way. If no when condition is true, then the result of the case expression is the value in the ELSE clause. If the ELSE clause is omitted and there is no matching condition, the result is NULL.

Or its syntax is:

Simple Case function
Case Sex
When ' 1 ' Then ' men '
When ' 2 ' then ' women '
Else ' other ' END

The advice is to use the first, less points, or understanding.

Example: Following a school in 2005 and 2006 the data of the tournament,

1) will win_loss in the win, negative, ping du into the corresponding ' win ', ' loss ', ' tie '

SelectDate_year, Case      whenWin_loss='win'  Then 'win'     whenWin_loss='Negative'  Then 'Loss'     Else 'Tie'     EndWin_loss fromScores

2) Assuming 3 points, a flat score, minus 0 points, the score of each year is counted

SelectDate_year,sum( Case  whenWin_loss='win'  Then 3  whenWin_loss='Flat'  Then 1 Else 0 End) score fromScoresGroup  byDate_year;

3) Count the number of wins per year, the number of flat fields and the number of negative fields

SelectDate_year,sum( Case  whenWin_loss='win'  Then 1 Else 0 End)'number of winning games' ,sum( Case  whenWin_loss='Negative'  Then 1 Else 0 End)'negative Field number',sum( Case  whenWin_loss='Flat'  Then 1 Else 0 End)'number of flat fields'  fromScoresGroup  byDate_year;

As you can see from example one, when you use case and then else, each statement is executed once.

Example two: The data set is as follows:

Try:

Select  Case  when = ' language '  Then Else 0 End  from Testscore

1) Use a line to show a student's score

Select Tname, Tscor  from Group  by Tname;

SelectTname, ( Case When ttype='language'  ThenTscorElse 0 End)'language', ( Case  whenTtype='Mathematics'  ThenTscorElse 0 End)'Mathematics', ( Case  whenTtype='English'  ThenTscorElse 0 End)'English' fromTestscoreGroup  byTname;

SelectTname as 'name' ,         Max( CaseTtype when 'language'  ThenTscorElse 0 End)'language',         Max( CaseTtype when 'Mathematics'  ThenTscorElse 0 End)'Mathematics',         Max( CaseTtype when 'English'  ThenTscorElse 0 End)'English'      fromTestscoreGroup  byTname;

SelectTname,Max( Case   whenTtype='language'  ThenTscorElse 0 End)'language', Max( Case  whenTtype='Mathematics'  ThenTscorElse 0 End)'Mathematics', Max( Case  whenTtype='English'  ThenTscorElse 0 End)'English' fromTestscoreGroup  byTname;

These two are the same results.

Compare the functions of the above, poly and function ....??

2) The total score of the students in arts and sciences.

SelectTname as 'name',        Case         whenTtype='Mathematics'  Then 'Science' Else 'Liberal Arts' End  as 'Branch',        sum(Tscor) as 'Total'     fromTestscoreGroup  byTname, Case               whenTtype='Mathematics'  Then 'Science' Else 'Liberal Arts' End;

Simple use of the when and then ... else end in MySQL