Some Usage of select case when and if

Overview:
The case statement in the SQL statement and switch statement in the advanced language are standard SQL syntaxes and are applicable to multiple condition judgments.

In case of different values, perform different operations.

First, let's take a look at the case syntax. In a general SELECT statement, the syntax format is as follows:
Select <mycolumnspec> =
Case <Single-value expression>
When <expression value> then <SQL statement or return value>
When <expression value> then <SQL statement or return value>
...
When <expression value> then <SQL statement or return value>
End

Example (reference ):
Group 1: query the number of all records in the dj_zt table with the status '07', '11', and qylx_dm = '03.
A: Use Case statements
Select count (Case A. ZT when '07 'Then A. BS end) +
Count (Case A. ZT when '11' then A. BS end)
From dj_zt
Where a. qylx_dm = '03'
----------------
11829

B: Case statements are not required.
Select count (*)
From dj_zt
Where a. qylx_dm = '03'
And a. ZT in ('07', '11 ')
----------------
11829

Result: Group A and Group B have the same cost. Compared with group B, group A and group B have a draw.

Group 2: query the number of all records in the dj_zt table with status '07 'and '11' and qylx_dm = '03' respectively.
A: Use Case statements
Select count (Case A. ZT when '07 'Then A. BS end ),
Count (Case A. ZT when '11' then A. BS end)
From dj_zt
Where a. qylx_dm = '03
----------------
4565 7264

B: The case statement is not used (two statements are written, and the table is scanned twice, which is obviously inefficient)
Select count (*)
From dj_zt
Where a. qylx_dm = '03'
And a. zt = '07'
----------------
4565

Select count (*)
From dj_zt
Where a. qylx_dm = '03'
And a. zt = '11'
----------------
7264

Result: The cost of group B is much higher than that of group A, and the execution efficiency is relatively low.

Differences between case and if:
In advanced languages, case can be replaced by if, but not in SQL.
Case is defined by the SQL standard, and if is an extension of the database system.
Case can be used for SQL statements, SQL stored procedures, and triggers. If can only be used for stored procedures and triggers.
In the SQL process and trigger, replacing case with IF has a high cost, which is quite troublesome and difficult to implement.

Conclusion: we can see from the above two groups of instances that the case statement can make the SQL statement concise and efficient, thus greatly improving the execution efficiency. In addition, the use of case generally does not cause low performance (compared to statements without case), but increases the flexibility of operations.

Select Atid, userid, title, releasedate, forumid, clicks, istoday = (
Case convert (varchar (10), releasedate, 120)
When convert (varchar (10), getdate (), 120)
Then releasedate
End), bbssettop from tab_articletopics where forumid <> 0 and status in (1, 5)
Order by bbssettop DESC, istoday DESC, clicks DESC

========================================================== ===
There is a table with three fields: Chinese, mathematics, and English. There are 3 records indicating 70 points in Chinese, 80 points in mathematics, and 58 points in English, please use an SQL statement to query these three records and display them according to the following conditions (and write your ideas ):
If the value is greater than or equal to 80, it indicates excellent. If the value is greater than or equal to 60, it indicates passing the test. If the value is less than 60, it indicates failing.
Display format:
Chinese, mathematics, and English
Pass excellent fail
------------------------------------------
Select
(Case when language> = 80 then 'excellent'
When language> = 60 then 'pass'
Else 'failed') as language,
(Case when mathematics> = 80 then 'excellent'
When mathematics> = 60 then 'pass'
Else 'failed') as mathematics,
(Case when English> = 80 then 'excellent'
When English> = 60 then 'pass'
Else 'failed') as English,
From table

 

--------------------------------------------------------------------------

If statement usage

 

If (expr1, expr2, expr3)

If expr1 is true (expr1 <> 0 and expr1 <> null), the return value of IF () is expr2; otherwise, the return value is expr3. The return value of IF () is a numeric or string value, depending on the context.

 

Mysql> select if (1> 2, 2, 3 );

-> 3

 

Mysql> select if (1 <2, 'yes', 'no ');

-> 'Yes'

 

Mysql> select if (strcmp ('test', 'test1'), 'No', 'yes ');

-> 'No'

 

If only one expr2 or expr3 is null, The result type of the IF () function is not the result type of the null expression.

 

Expr1 is calculated as an integer. That is to say, if you are verifying a floating point or string value, you should use a comparison operation for testing.

 

Mysql> select if (0.1 );

-> 0

 

Mysql> select if (0.1 <>, 0 );

-> 1

 

In the first example, if (0.1) returns 0 because 0.1 is converted to an integer, resulting in a test of IF (0. This may not be what you want. In the second example, a comparison checks the original floating point value to see if it is a non-zero value. The comparison result uses an integer.

The default Return Value Type of IF () (which is important when it is stored in a temporary table) is calculated as follows:

Expression

Return Value

The return value of expr2 or expr3 is a string.

String

 

The return value of expr2 or expr3 is a floating point value.

Floating Point

 

The return value of expr2 or expr3 is an integer.

Integer

If both expr2 and expr3 are strings, and any one of them is case sensitive, the returned result is case sensitive.

Ifnull (expr1, expr2)

 

If expr1 is not null, the returned value of ifnull () is expr1; otherwise, the returned value is expr2. The returned value of ifnull () is a number or string, depending on the context in which it is used.

Mysql> select ifnull (1, 0 );

-> 1

 

Mysql> select ifnull (null, 10 );

-> 10

 

Mysql> select ifnull (1/0, 10 );

-> 10

 

Mysql> select ifnull (1/0, 'yes ');

-> 'Yes'

 

The default result value of ifnull (expr1, expr2) is one of the two expressions that is more "common" in the order of string, real, or integer. Assume that an expression-based table or MySQL must store the returned value of ifnull () in a temporary table in internal memory:

 

Create Table TMP select ifnull (1, 'test') as test;

 

In this example, the test column type is Char (4 ).

 

Nullif (expr1, expr2)

 

If expr1 = expr2 is true, the return value is null. Otherwise, the return value is expr1. This is the same as case when expr1 = expr2 then null else expr1 end.

 

Mysql> select nullif (1, 1 );

-> Null

 

Mysql> select nullif (1, 2 );

-> 1

 

Note: If the parameters are not equal, the value obtained by MySQL twice is expr1.

 

From: http://hi.baidu.com/river2005/blog/item/98222d019e2ea3047bec2c83.html

Http://blog.163.com/fantasy_lxh/blog/static/8776435020096282199595/