Cấp bậc tác giả:

DATABASE

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

Được viết bởi QuangIT ngày 13/08/2012 lúc 07:43 PM
Trong phần một và phần hai của loạt bài này, chúng tôi đã giải thích cách sử dụng các hàm CASE đơn giản trong truy vấn. Phần này sẽ hướng dẫn cách sử dụng hàm CASE trong các mệnh đề như GROUP BY
  • 0
  • 2544

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


Trong phần một và phần hai của loạt bài này, chúng tôi đã giải thích cách sử dụng các hàm CASE đơn giản trong truy vấn. Phần này sẽ hướng dẫn cách sử dụng hàm CASE trong các mệnh đề như GROUP BY

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

Kết luận 

Trong ba phần của loạt bài này, chúng tôi đã giải thích cách sử dụng các hàm CASE trong SQL Server với nhiều giả thuyết khác nhau. Trong phần tiếp theo, chúng ta sẽ tiếp tục nghiên cứu cách sử dụng câu lệnh này trong mệnh đề IN.

Nguồn bài viết: QuanTriMang

BÌNH LUẬN BÀI VIẾT

Bài viết mới nhất

LIKE BOX

Bài viết được xem nhiều nhất

HỌC HTML