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:

SELECT Id,name, case Sex when 0 then  ' 

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.

SELECT Id,name, sex = case > Sex --difference is simply to place the alias here when 0 then   male  ' 

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 --note there's nothing behind this case. when sex=0 then   handsome  '--of course Sex you can switch to any other column when sex=1 then   beauty  ' 

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,CaseWhen Id=3and Sex=0Then‘Handsome guy‘--Here to note the order, the precedence shows the previous match to, this later will mentionWhen Id=4and Sex=1Then‘Beauty' when sex = 0 then ' man ' when sex = 1 then ' women ' ELSE ' c16> ' not clear ' END as gender         from person 

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,CaseWhen score>90Then‘Excellent‘When score>80Then‘Good ' when score > 70 then   medium  ' 

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:

  Select Id,name,sex from person order by case when sex=0 then Id end desc, Span style= "color: #008000;" >--sex is male, id descending case when sex =1  Then Id end asc --sex is female, ID ascending                 
 

The results appear as follows:

　　

T-SQL column-based logical expression (case)