Case
It may be one of the keywords that are misused in SQL. Although you may have used this keyword to create a field, it also has more usage. For example, you can
WHERE
Use in clauses
Case
. Let us first let us look
Case
Syntax. In general
SELECT
In the middle, its grammar is as follows:
SELECT
<
MyColumnSpec
>
=
Case
WHEN
<
A
>
THEN
<
Somethinga
>
WHEN
<
B
>
THEN
<
Somethingb
>
Else
<
Something
>
End
In the above code, the specific parameter is required instead of the contents of the angle brackets. Here 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
Typical usage, but use
Case
In fact, you can do more things. Below
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, add one
ORDER
BY
The clause 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
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
ISNULL
THEN
'
Unpriced
'
WHEN
PRICE
<
10
THEN
'
Bargain
'
WHEN
PRICE
Between
10
and
20
THEN
'
Average
'
Else
'
Gift to IMPRESS Relatives
'
End
Title
Go
Pay attention, in order to
Group
BY
Block in block
Case
, Query statement needs
Group
BY
Duplicate block
SELECT
Block
Case
Piece. In addition to selecting a custom field, in many cases
Case
It is very useful. Step by step, you can also get the grouped sort result set you have previously believed.
