Hàm CASE trong SQL Server 2005 (Phần 4)

Trong phần 1, phần 2 và phần 3 của loạt bài này, chúng tôi đã giới thiệu cách sử dụng các hàm CASE đơn giản trong truy vấn. Trong phần tiếp theo này, tôi sẽ giải thích cách sử dụng các hàm CASE trong mệnh đề như ‘Group by’

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

Kết luận

Trong 4 phần đầu đã được giới thiệu của loạt bài này, chúng tôi đã đưa ra các cách sử dụng của hàm CASE của SQL Server trong một số trường hợp cụ thể. Phần 4 này đã giải thích cách sử dụng hàm CASE trong mệnh đề IN như đã được đề cập trong các phần đầu của bài.