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 CASE 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
- Phương thức 2: Sử dụng hàm case đơn giản với mệnh đề ELSE
- 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
- Phương thức 4: Sử dụng hàm CASE trong tìm kiếm
- Phương thức 5: Sử dụng hàm CASE trong mệnh đề ORDER BY
- Phương thức 6: Sử dụng hàm CASE đơn giản trong mệnh đề GROUP BY
- Phương thức 7: Sử dụng hàm CASE đơn giản trong mệnh đề GROUP BY
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
Sau khi chạy lệnh trên ta có bảng sau:
-+----+------------+-----------+-------+
| id | First name | Last name |gender |
| 1 | John | Smith | m |
| 2 | James | Bond | m |
| 3 | Alexa | Mantena | f |
| 4 | Shui | Qui | f |
| 5 | William | Hsu | m |
| 6 | Danielle | Stewart | F |
| 7 | Martha | Mcgrath | F |
| 8 | Henry | Fayol | m |
| 9 | Dick | Watson | m |
| 10 | Helen | Foster | F |
+----+------------+-----------+-------+
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 | NUL |
+----+---------------------+
Tuy nhiên, chúng ta cần hiển thị Full Name (họ tên đầy đủ) của nhân viên, kể cả trong trường hợp không có giá trị trên Gender.
Để thực hiện điều này, bạn cần dùng hàm CASE với mệnh đề ELSE. Thực hiện truy vấn như sau:
Select [id],[Full Name] = case Gender
when 'm' then 'Mr. '+[First name]+ ' '+[Last name]
when 'f' then 'Mz. '+[First name]+ ' '+[Last name]
else [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 | Tom Gabe |
+----+---------------------+
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 Dummies', 'Judith Hurwitz','NJ') insert into Books (Bookid, Title, Authorname, state) values (3, 'Business Reference for Students and Professionals','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 Dummies','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 to Organizational Performance', '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 | | |
|Professionals |Ray Myers | NY |
+------------------------------------+---------------+-------+
|How to become CEO |Olga Zohaskov | NY |
+------------------------------------+---------------+-------+
|The Third Eye |Lobsang Rampa | CA |
+------------------------------------+---------------+-------+
|Service Oriented Architecture For | | |
|Dummies |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 Competencies| | |
| to Organizational Per |John Smith | FL |
+------------------------------------+---------------+-------+
|Performance Appraisals: How to |Priscilla | |
|Achieve Top Results |A. Glidden | NC |
+------------------------------------+---------------+-------+
Ảnh chụp trong SMSS sẽ như sau:
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
AverageSalary TimeZone --------------------------- 124000.00 Alaskan 50000.00 Central 188666.6666 Eastern 200000.00 Mountain 72500.00 Pacific
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:
AverageSalary TimeZone
-----------------------------
124000.00 Alaskan
188666.6666 Eastern
Phương thức 7: 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à bây giờ chúng ta cần có 6 bảng để lưu trữ các ID của nhân viên thuộc các vùng thời gian khác nhau như trình bày sau
if exists (select * from dbo.sysobjects where id = object_id(N'[eastern]') and objectproperty(id, N'isusertable') = 1) drop table [eastern] go create table eastern (id int) if exists (select * from dbo.sysobjects where id = object_id(N'[mountain]') and objectproperty(id, N'isusertable') = 1) drop table [mountain] go create table mountain (id int) if exists (select * from dbo.sysobjects where id = object_id(N'[hawaii]') and objectproperty(id, N'isusertable') = 1) drop table [hawaii] go create table hawaii (id int) if exists (select * from dbo.sysobjects where id = object_id(N'[central]') and objectproperty(id, N'isusertable') = 1) drop table [central] go create table central (id int) if exists (select * from dbo.sysobjects where id = object_id(N'[alaskan]') and objectproperty(id, N'isusertable') = 1) drop table [alaskan] go create table alaskan (id int) if exists (select * from dbo.sysobjects where id = object_id(N'[pacific]') and objectproperty(id, N'isusertable') = 1) drop table [pacific] go create table pacific (id int) go insert into pacific (id) values (1) insert into pacific (id) values (2) insert into mountain (id) values (3) insert into mountain (id) values (4) insert into central (id) values (5) insert into central (id) values (6) insert into eastern (id) values (7) insert into eastern (id) values (8) insert into eastern (id) values (9) insert into alaskan (id) values (10) go
Nếu bạn muốn biết toàn bộ nhân viên thuộc vùng thời gian ở Eastern, bạn chắc chắn sẽ phải thực thi một câu lệnh truy vấn đơn giản như dưới đây
select e.id,[First Name],[Last name], Salary, State from emp e join eastern ee on e.id=ee.id
Câu lệnh truy vấn trên sẽ trả về kết quả như sau
id First name Last name salary Timezone
------------------------------------------------
7 Martha Mcgrath 400000.00 PA
8 Henry Fayol 75000.00 NJ
9 Dick Watson 91000.00 NY
Vậy bây giờ giả sử chúng ta cần tạo một kịch bản cho phép đưa một khu vực thời gian vào trong một biến và hiển thị ra kết quả dựa trên giá trị của biến đó. Điều này hoàn toàn có thể làm được khi sử dụng mệnh đề và hàm CASE như sau:
declare @group varchar(10) set @group='Pacific' select ee.id,ee.[First Name],ee.[Last Name],Salary, State, @group as TimeZone from emp ee left join mountain m on m.[id]=ee.[id] left join alaskan a on a.[id]=ee.[id] left join hawaii h on h.[id]=ee.[id] left join central c on c.[id]=ee.[id] left join pacific p on p.[id]=ee.[id] left join eastern e on e.[id]=ee.[id] where ee.id in (case @group when 'Eastern' then e.id when 'Mountain' then m.id when 'Pacific' then p.id when 'Alaskan' then a.id when 'Hawaii' then h.id when 'Central' then c.id end)
Đoạn kịch bản trên sẽ có kết quả như sau:
id First name Last name salary state TimZone
-------------------------------------------------------
1 John Smith 120000.00 WA Pacific
2 James Bond 95000.00 OR Pacific
Đoạn script trên có thể được viết trong một thủ tục như sau:
create procedure emp_timezone @Zone varchar(10) as select ee.id,ee.[First Name],ee.[Last Name],Salary, State, @Zone as TimeZone from emp ee left join mountain m on m.[id]=ee.[id] left join alaskan a on a.[id]=ee.[id] left join hawaii h on h.[id]=ee.[id] left join central c on c.[id]=ee.[id] left join pacific p on p.[id]=ee.[id] left join eastern e on e.[id]=ee.[id] where ee.id in ( case @Zone when 'Eastern' then e.id when 'Mountain' then m.id when 'Pacific' then p.id when 'Alaskan' then a.id when 'Hawaii' then h.id when 'Central' then c.id end)
Và thực thi hàm đã tạo ở trên bằng câu lệnh:
exec emp_timezone 'Eastern'
Hàm sẽ đưa ra kết quả:
id First name Last name salary state TimZone
------------------------------------------------------
7 Martha Mcgrath 400000.00 PA Eastern
8 Henry Fayol 75000.00 NJ Eastern
9 Dick Watson 91000.00 NY Eastern
6 Th1 2021
8 Th1 2021
31 Th12 2020
16 Th12 2020
23 Th12 2020
12 Th1 2021