How to Use case when syntax in SQL

Source: Internet
Author: User

No. Use case when instead.

For example, the following statement shows the Chinese year and month
Copy codeThe Code is as follows:
Select getdate () as date, case month (getdate ())
When 11 then '11'
When 12 then '12'
Else substring ('february 5, 1234, 80 or 90 ', month (getdate (), 1)
End + 'month' as month

CASE 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 use CASE in the WHERE clause.

First, let's take a look at the CASE syntax. In a general SELECT statement, the syntax is as follows:
Copy codeThe Code 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:
Copy codeThe Code is as follows:
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 a typical example of CASE, but you can do more with CASE. For example, the CASE in the group by clause below:
Copy codeThe Code is as follows:
SELECT 'number of tidles ', Count (*)
FROM titles
GROUP
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 an order by clause, as shown below:
Copy codeThe Code is 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
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
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: To use CASE in the group by block, the query statement must repeat the CASE block in the SELECT block in the group by block.

In addition to selecting custom fields, CASE is useful in many cases. Further, you can get the grouping sorting result set that you previously thought was impossible to get.

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.