Different case usage in SQL Server

Source: Internet
Author: User
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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.