Case It may be one of the keywords most misused in SQL. Although you may have used this keyword before to create a field, it also has more features. For example, you can Where Clause Case .
First, let's take a look. Case . In normal Select The syntax is as follows:
Select < Mycolumnspec > =
Case
When < A > Then < Somethinga >
When < B > Then < Somethingb >
Else < Somethinge >
End
In the above Code, you need to replace the content in angle brackets with specific parameters. The following is a simple example:
Use Pubs
Go
Select
Title,
' Price Range ' =
Case
When Price Is Null Then ' Unpriced '
When Price < 10 Then ' Bargain '
When Price Between 10 And 20 Then ' Average '
Else ' Gift to impress relatives '
End
From Titles
Order By Price
Go
This is Case But Case In fact, more things can be done. For example Group By Clause Case :
Select ' Number of titles ' , Count ( * )
From Titles
Group By
Case
When Price Is Null Then ' Unpriced '
When Price < 10 Then ' Bargain '
When Price Between 10 And 20 Then ' Average '
Else ' Gift to impress relatives '
End
Go
You can even combine these options to add Order By As follows:
Use Pubs
Go
Select
Case
When Price Is Null Then ' Unpriced '
When Price < 10 Then ' Bargain '
When Price Between 10 And 20 Then ' Average '
Else ' Gift to impress relatives '
End As Range,
Title
From Titles
Group By
Case
When Price Is Null Then ' Unpriced '
When Price < 10 Then ' Bargain '
When Price Between 10 And 20 Then ' Average '
Else ' Gift to impress relatives '
End ,
Title
Order By
Case
When Price Is Null Then ' Unpriced '
When Price < 10 Then ' Bargain '
When Price Between 10 And 20 Then ' Average '
Else ' Gift to impress relatives '
End ,
Title
Go
Note that in order Group By Block Used in Case , The query statement must be in Group By Block already exists Select Block Case Block.
In addition to custom fields Case Are very useful. Further, you can get the grouping sorting result set that you previously thought was impossible to get.
From: http://www.g22.net/Article/Show.asp? Id = 688
