Detailed explanation of usage of sql statement case when

sql statement case when usage

When we need to directly determine the meaning of the data display from the data source, we can use SQL The Case When function is used in the statement.

Case has two formats. Simple Case function and Case search function.

The first format: Simple Case function:

Format description

case column name

when condition value 1 then option 1

when condition value 2 then option 2....

else default value end

select
　　　　case 　　job_level
　　　　when     '1'     then    '1111'
　　　　when　   '2'     then    '1111'
　　　　when　   '3'     then    '1111'
　　　　else     'eee' end
　　　　from     dbo.employee

Second format: Case search function

Format description

case

when column name = condition value 1 then selection item 1

when column name = condition value 2 then Option 2......

Else Default value end

eg:
　　　update   employee
　　　　set    e_wage =
　　　　case
　　　　when   job_level = '1'    then e_wage*1.97
　　　　when   job_level = '2'   then e_wage*1.07
　　　　when   job_level = '3'   then e_wage*1.06
　　　　else   e_wage*1.05
　　　　end

Tip: Usually when we write a Case When statement, it is easy to forget the end. ,Be sure to remember!

Comparison: Two formats can achieve the same function.

The simple Case function is relatively simple to write, but compared with the Case search function, there are some limitations in functionality, such as writing judgments. There is another issue that needs attention. The Case function only returns the first qualified value, and the remaining Case parts will be automatically ignored.

Recommended tutorial: "sql tutorial"

The above is the detailed content of Detailed explanation of usage of sql statement case when. For more information, please follow other related articles on the PHP Chinese website!