Hàm CASE trong SQL Server

Trong SQL Sever, hàm CASE kiểm định giá trị dựa trên danh sách điều kiện đưa ra, sau đó trả về một hoặc nhiều kết quả. Ở bài này chúng tôi sẽ minh hoạ một số cách dùng khác nhau của hàm này trong những trường hợp khác nhau.

Phương thức 1: Cách dùng hàm CASE đơn giản

Đây là cách dùng phổ biến nhất của hàm case, trong đó bạn có thể tạo giá trị vô hướng dựa trên danh sách điều kiện đưa ra.

Giả sử chúng ta có bảng sau với các cột id (mã số nhân viên), [First name] (tên), [Last name] (họ) và gender (giới tính). Bây giờ, chúng ta muốn tạo thêm tiền tố (Mr, Ms) ở phía trước từng tên, dựa trên giá trị của cột Gender, thực hiện như sau:

use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[emp]') 
 and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [emp]
GO
create table Emp (id int, [First name] varchar(50),
 [Last name] varchar(50), gender char(1))
go
insert into Emp (id,[First name],[Last name], gender ) 
 values (1,'John','Smith','m')
insert into Emp (id,[First name],[Last name], gender ) 
 values (2,'James','Bond','m')
insert into Emp (id,[First name],[Last name], gender ) 
 values (3,'Alexa','Mantena','f')
insert into Emp (id,[First name],[Last name], gender ) 
 values (4,'Shui','Qui','f')
insert into Emp (id,[First name],[Last name], gender ) 
 values (5,'William','Hsu','m')
insert into Emp (id,[First name],[Last name], gender ) 
 values (6,'Danielle','Stewart','F')
insert into Emp (id,[First name],[Last name], gender ) 
 values (7,'Martha','Mcgrath','F')
insert into Emp (id,[First name],[Last name], gender ) 
 values (8,'Henry','Fayol','m')
insert into Emp (id,[First name],[Last name], gender ) 
 values (9,'Dick','Watson','m')
insert into Emp (id,[First name],[Last name], gender )
 values (10,'Helen','Foster','F')
go

Bây giờ, tạo cột [Full name] (họ tên đầy đủ) để xác định nên đặt tiền tố là “Mr.” hay “Ms.”, dựa vào giá trị trên cột Gender.

Select [id],[Full Name] = case Gender
 when 'm' then 'Mr. '+[First name]+ ' '+[Last name]
 when 'f' then 'Ms. '+[First name]+ ' '+[Last name] 
 end
from Emp

Thủ tục này trả về kết quả như hình bên dưới:

id          Full Name
----------- ----------------
1           Mr. John Smith
2           Mr. James Bond
3           Ms. Alexa Mantena
4           Ms. Shui Qui
5           Mr. William Hsu
6           Ms. Danielle Stewart
7           Ms. Martha Mcgrath
8           Mr. Henry Fayol
9           Mr. Dick Watson
10          Ms. Helen Foster

Phương thức 2: Sử dụng hàm case đơn giản với mệnh đề ELSE
Nếu thêm một hàng với giá trị NULL vào cột gender, bạn sẽ không thấy có tên nào được trả về trong tập hợp kết quả.
Chèn hàng sau vào bảng emp:

use tempdb
go
insert into Emp (id,[First name],[Last name], gender )
 values (11,'Tom','Gabe',NULL)
go

Bây giờ tạo cột [Full name] để xác định tiền tố đặt trước mỗi tên là “Mr.” hay “Ms.”, dựa trên giá trị lấy ở cột Gender:

Select [id],[Full Name] = case Gender
 when 'm' then 'Mr. '+[First name]+ ' '+[Last name] 
 when 'f' then 'Ms. '+[First name]+ ' '+[Last name] 
 end
from Emp

Kết quả trả về như sau:

id          Full Name
----------- ------------------------
1           Mr. John Smith
2           Mr. James Bond
3           Ms. Alexa Mantena
4           Ms. Shui Qui
5           Mr. William Hsu
6           Ms. Danielle Stewart
7           Ms. Martha Mcgrath
8           Mr. Henry Fayol
9           Mr. Dick Watson
10          Ms. Helen Foster
11          NULL

Phương thức 3: Sử dụng hàm CASE khi có hai hoặc nhiều điều kiện trong danh sách

Trong hai ví dụ trên, bạn thấy rằng các điều kiện được xét đến hoặc là Male, Female, hay None. Tuy nhiên, trong nhiều trường hợp bạn sẽ cần sử dụng nhiều điều kiện, nhiều toán tử cùng lúc để trả về một giá trị.

Thêm cột [Marital Status] (tình trạng hôn nhân) vào bảng và update giá trị như bên dưới:

use tempdb
go
alter table Emp add [Marital Status] char(1)  -- S-Single M-Married 
go
Update Emp set [Marital Status]='S' where id in (1,5,8)
Update Emp set [Marital Status]='M' where [Marital Status] is NULL
Go

Giả sử chúng ta muốn hiển thị tên nhân viên có tiền tố đứng trước giúp dễ dàng xác định tình trạng hôn nhân của họ. Thực hiện truy vấn như sau:

Select [id],[Full Name] = case 
when Gender ='m' and [marital status] ='S' 
    then 'MR. '+[First name]+ ' '+[Last name] 
when Gender ='m' and [marital status] ='M' 
    then 'Mr. '+[First name]+ ' '+[Last name] 
when Gender ='f' and [marital status] ='S' 
    then 'Ms. '+[First name]+ ' '+[Last name] 
when Gender ='f' and [marital status] ='M' 
    then 'Mrs. '+[First name]+ ' '+[Last name] 
else [First name]+ ' '+[Last name] 
 end
from Emp

Kết quả được trả về là:

id          Full Name
----------- --------------------
1           MR. John Smith
2           Mr. James Bond
3           Mrs. Alexa Mantena
4           Mrs. Shui Qui
5           MR. William Hsu
6           Mrs. Danielle Stewart
7           Ms. Martha Mcgrath
8           MR. Henry Fayol
9           Mr. Dick Watson
10          Mrs. Helen Foster
11          Tom Gabe

Phương thức 4: Sử dụng hàm CASE trong tìm kiếm
Giả sử chúng ta có bảng sau

 

use tempdb
go
if exists (select * from dbo.sysobjects
where id=object_id(N'[emp]')and OBJECTPROPERTY(id, N'IsUserTable')=1)
drop table [emp]
GO
create table Emp
(id int,[First name] varchar(50),[Last name] varchar(50),Salary money)
go
insert into Emp (id,[First name],[Last name], salary )
values (1,'John','Smith',120000)
insert into Emp (id,[First name],[Last name], salary )
values (2,'James','Bond',95000)
insert into Emp (id,[First name],[Last name], salary )
values (3,'Alexa','Mantena',200000)
insert into Emp (id,[First name],[Last name], salary )
values (4,'Shui','Qui',36000)
insert into Emp (id,[First name],[Last name], salary )
values (5,'William','Hsu',39000)
insert into Emp (id,[First name],[Last name], salary )
values (6,'Danielle','Stewart',50000)
insert into Emp (id,[First name],[Last name], salary )
values (7,'Martha','Mcgrath',400000)
insert into Emp (id,[First name],[Last name], salary )
values (8,'Henry','Fayol',75000)
insert into Emp (id,[First name],[Last name], salary )
values (9,'Dick','Watson',91000)
insert into Emp (id,[First name],[Last name], salary )
values (10,'Helen','Foster',124000)
go

Và giờ muốn tạo thêm một cột Tax (thuế) dựa trên mức lương như sau

Select [id],[Full Name]=[First name]+ [Last name],Salary,Tax=case
When  salary between 0 and 36000 then Salary*.24
When  salary between 36000 and 450000 then Salary*.28
When  salary between 45000 and 75000 then Salary *.30
When  salary between 75000 and 150000 then Salary *.32
else Salary*.40 end
from Emp

Hàm này sẽ cho kết quả:

id          Full Name          Salary                Tax
----------- -------------------------------- ---------------------
1           JohnSmith         120000.00             33600.000000
2           JamesBond          95000.00             26600.000000
3           AlexaMantena      200000.00             56000.000000
4           ShuiQui            36000.00              8640.000000
5           WilliamHsu         39000.00             10920.000000
6           DanielleStewart    50000.00             14000.000000
7           MarthaMcgrath     400000.00            112000.000000
8           HenryFayol         75000.00             21000.000000
9           DickWatson         91000.00             25480.000000
10          HelenFoster       124000.00             34720.000000

Phương thức 5: Sử dụng hàm CASE trong mệnh đề ORDER BY
Giả sử chúng ta có bảng dưới trong Books:

 

use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[Books]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Books]
GO
create table Books
(Bookid int, Title varchar(100),Authorname varchar(100), state char(2))
go
insert into Books (Bookid, Title, Authorname, state)
values (1, 'The Third Eye','Lobsang Rampa','CA')
insert into Books (Bookid, Title, Authorname, state)
values (2, 'Service Oriented Architecture For ','Judith Hurwitz','NJ')
insert into Books (Bookid, Title, Authorname, state)
values (3, 'Business Reference for Students and','Ray Myers','NY')
insert into Books (Bookid, Title, Authorname, state)
values (4, 'More Java Gems','Dwight Deugo', 'FL')
insert into Books (Bookid, Title, Authorname, state)
values (5, 'Six Sigma Workbook For ','Craig Gygi','FL')
insert into Books (Bookid, Title, Authorname, state)
values (6, 'Performance Appraisals: How to Achieve Top Results',
'Priscilla A. Glidden', 'NC' )
insert into Books (Bookid, Title, Authorname, state)
values (7, 'Talent Management: From Competencies ',
'John Smith','FL')
insert into Books (Bookid, Title, Authorname, state)
values (8, 'Using Unix','Howard Johnson','CT')
insert into Books (Bookid, Title, Authorname, state)
values (9, 'Mastering Oracle','Erina Zolotrova','CT')
insert into Books (Bookid, Title, Authorname, state)
values (10, 'How to become CEO','Olga Zohaskov','NY')
go

Để truy vấn tất cả các giá trị trong bảng ta sử dụng hàm truy vấn dưới:

Select * from Books

Hàm này sẽ cho kết quả như hình dưới

Giả sử chúng ta muốn hiển thị toàn bộ số sách theo thứ tự các bang: đầu tiên là NY
sau đó là CA, NJ, CT và FL.

Bạn có thể thực hiện được điều này bằng cách sử dụng hàm CASE như dưới đây:

select Title, Authorname, state from Books order by case
when state ='NY' then 1
when state ='CA' then 2
when state ='NJ' then 3
when state ='CT' then 4
when state ='FL' then 5 else 6 end

Hàm này sẽ cho kết quả như sau:

Title                                   Authorname               state
-------------------------------------- -----------------------  -----
Business Reference for Students and     Ray Myers               NY
How to become CEO                       Olga Zohaskov           NY
The Third Eye                           Lobsang Rampa           CA
Service Oriented Architecture For       Judith Hurwitz          NJ
Using Unix                              Howard Johnson          CT
Mastering Oracle                        Erina Zolotrova         CT
More Java Gems                          Dwight Deugo            FL
Six Sigma Workbook For Dummies          Craig Gygi              FL
Talent Management: From Competenci      Per John Smith          FL
Performance Appraisals: How to Achieve  Priscilla A. Glidden    NC

Phương thức 6: Sử dụng hàm CASE đơn giản trong mệnh đề GROUP BY

Giả sử chúng ta có bảng sau

 

set quoted_identifier off
go
use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[emp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [emp]
GO
create table Emp (id int, [First name] varchar(50),
[Last name] varchar(50), Salary money, state char(2))
go
insert into Emp (id,[First name],[Last name], salary, State )
values (1,'John','Smith',120000,'WA')
insert into Emp (id,[First name],[Last name], salary, State )
values (2,'James','Bond',95000,'OR')
insert into Emp (id,[First name],[Last name], salary , State)
values (3,'Alexa','Mantena',200000,'WY')
insert into Emp (id,[First name],[Last name], salary, State )
values (4,'Shui','Qui',36000,'CO')
insert into Emp (id,[First name],[Last name], salary, State )
values (5,'William','Hsu',39000,'NE')
insert into Emp (id,[First name],[Last name], salary , State)
values (6,'Danielle','Stewart',50000,'TX')
insert into Emp (id,[First name],[Last name],
salary , State) values (7,'Martha','Mcgrath',400000,'PA')
insert into Emp (id,[First name],[Last name],
salary, State ) values (8,'Henry','Fayol',75000,'NJ')
insert into Emp (id,[First name],[Last name],
salary, State ) values (9,'Dick','Watson',91000,'NY')
insert into Emp (id,[First name],[Last name],
salary, State ) values (10,'Helen','Foster',124000,'AK')
go

 

Và giờ muốn tạo thêm cột TimeZone (thời gian theo vị trí địa lý) dựa vào State (bang của Mỹ)

 

select id,[First name],[Last name], salary, Timezone = case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in
('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD',
'DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in
('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA','WI','IL',
'TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
from emp

Hàm trên sẽ cho kết quả như sau

 

id First name   Last name     salary      Timezone
-------------------------------------------------------
1  John         Smith       120000.00     Pacific
2  James        Bond         95000.00     Pacific
3  Alea         Mantena     200000.00     Mountain
4  Shui         Qui          36000.00     Pacific
5  William      Hsu          39000.00     Pacific
6  Danielle     Stewart      50000.00     Central
7  Martha       Mcgrath     400000.00     Eastern
8  Henry        Fayol        75000.00     Eastern
9  Dick         Watson       91000.00     Eastern
10 Helen        Foster      124000.00     Alaskan

Giờ chúng ta lại muốn xem tất cả thông tin ở các dòng có Timezone là Eastern và Mountain

 

select * from (
select id,[First name],[Last name], salary, Timezone = case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in
('NY','NJ','VT','ME','NH','MA','RI','CT','PA','DE','MD',
'DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') then 'Eastern'
when state in
('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in
('ND','SD','NE','KS','OK','TX','MN','IA','MO','AR','LA',
'WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Hawaii' end
from emp) as mytype where TimeZone in ('Mountain','eastern')

Kết quả hàm này như sau

id First name Last name       salary       Timezone
3  Alexa      Mantena       200000.00      Mountain
7  Martha     Mcgrath       400000.00      Eastern
8  Henry      Fayol          75000.00      Eastern
9  Dick       Watson         91000.00      Eastern

Giờ chúng ta có bảng trên và bạn lại muốn hiển thị giá trị trung bình của lương dựa trên vùng thời gian (Timezone)

select avg(salary) as AverageSalary, Timezone = case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA',
    'DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') 
    then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO',
    'AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
from emp group by
case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA',
    'DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') 
    then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO',
    'AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end

Hàm này sẽ cho kết quả như hình dưới

Giờ bạn lại chỉ muốn xem vùng thời gian ở Eastern và Alaskan từ kết quả trên. Chũng ta có thể sử dụng mệnh đề HAVING như dưới đây

 

select avg(salary) as AverageSalary, Timezone = case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA',
    'DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') 
    then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO',
    'AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
from emp group by
case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA',
    'DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') 
    then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO',
    'AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
having
case
when state in ('WA','OR','NE','CO') then 'Pacific'
when state in ('NY','NJ','VT','ME','NH','MA','RI','CT','PA',
    'DE','MD','DC','VA','WV','MI','IN','OH','KY','NC','GA','FL') 
    then 'Eastern'
when state in ('MT','ID','WY','UT', 'CO','AZ','NM')  then 'Mountain'
when state in ('ND','SD','NE','KS','OK','TX','MN','IA','MO',
    'AR','LA','WI','IL','TN','MS','AL')  then 'Central'
when state in ('AK') then 'Alaskan'
when state in ('HA') then 'Hawaii' end
in ('Eastern','Alaskan')

Kết quả của hàm thể hiện trong hình sau