T-SQL column-based logical expression (case)

Case Brief

A column-based logical expression is actually a case expression. Can be used after Select,update,delete,set and in,where,order by and having clauses. Since this is a T-SQL query, only the case expression is used in the SELECT clause and the ORDER BY clause.

The action of the case expression with the IF in the programming language ... Then ... else logic is similar. Just a case expression does not control the flow of a T-SQL program in T-SQL, but only as a column-based logical use.

For a simple example, suppose you have a table like this:

　　

In the query, for the column sex, if it is false to show the male, if true to show the female.

The statements are as follows:

SELECTId,name, CaseSex when 0  Then 'male'     　　  when 1  Then 'female'      　　 ELSE 'not clear'      　　 END  asSex fromPerson

The results appear as follows:

　　

The actual case expression can be divided into two types:

• Case Simple expression: compares an expression to a set of simple expressions to determine the result.
• Case search expression (case searched expression): Computes a set of Boolean expressions to determine the result.

Each of these approaches is described below.

Case -Simple expression

In the case of a simple expression, the entire expression will only take a column of values to make a corresponding judgment. Just like the example above, now only gives another equivalent notation.

SELECTid,name, Sex=  CaseSex --the difference is just putting the alias here.  when 0  Then 'male'     when 1  Then 'female'     ELSE 'not clear'     END fromPerson

Because the value of the case expression is confined to only one column, the value data type after then must be the same or compatible, otherwise an error will be given.

In the above statement, there is also an optional "else" statement, which can be omitted, but the best practice is to keep else, otherwise all values that are not within the range of the matched value will be "NULL".

Case -Search Expression

Unlike case simple expressions, case search expressions provide more powerful functionality, and case search expressions not only use more complex logical expressions, but can also determine the values of displayed columns based on the data of multiple columns.

So how to determine the display value according to the column, and see the following example:

SELECTid,name, Sex= Case --notice there's nothing behind this case.      whenSex=0  Then 'Handsome'--Of course sex you can switch to any other column     whenSex=1  Then 'Beauty'    ELSE 'not clear'     END fromPerson

The results appear as follows:

　　

Now, let's do an example of judging by multiple columns, adding two records to the original table.

　　

　　Multiple column combinations to determine:

Now to judge by the ID and sex two columns to determine the handsome guy, beautiful SQL is as follows:

SELECTId,name, Case     whenId= 3  andSex= 0  Then 'Handsome' --note the order here, prioritize the previous match, and this will refer  to     whenId= 4  andSex= 1  Then 'Beauty'      whenSex= 0  Then 'male'     whenSex= 1  Then 'female'    ELSE 'not clear'    END  asSex fromPerson

The results appear as follows:

　　

　　Scope Judgment:

Create a new table as follows:

　　

The effect here is that when the score is greater than 90, show excellent, greater than 80, display well ...

The SQL statements are as follows:

SELECTId,name, Case  　　  whenScore>  -  Then 'Excellent' 　　  whenScore>  the  Then 'Good' 　　  whenScore>  -  Then 'Medium' whenScore>  -  Then 'Pass' 　　 ELSE 'inferior lattice' 　　 END  asscore fromScore

The query results are as follows:

　　

Here to pay attention to when ... Then occurs in a sequential order, and when the expression after the first when is false, the expression after the second when is seen, and so on. when the expression after the first when is true, the value after the first then is taken, even if the second when expression is true. So here's why the Liu Bei score satisfies the previous conditions and also shows the first result.

use of the case expression in order by

　　The case expression can classify the sorted result in order by, so that it can be in ascending order according to certain conditions, or descending in accordance with some other condition, but in general, the following is an example.

Or just that table, I think, when the gender for the male ID descending sort, if the gender is the female ID ascending sort. The SQL statements are as follows:

SELECTId,name,sex from PersonORDER  by      　　  Case  whenSex=0  ThenIdEND DESC,--Gender is male, ID descending     　　  Case  whenSex=1  ThenIdEND ASC      --Sex for female, id ascending

The results appear as follows:

　　

T-SQL column-based logical expressions (case)