Hàm CASE về SQL Server

Chia sẻ: Pt Pt | Ngày: | Loại File: PDF | Số trang:10

Thêm vào BST

Báo xấu

113
lượt xem 10
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

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...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Hàm CASE về SQL Server

Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com Hàm CASE trong SQL Server (phần 1) 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')
Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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
Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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 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:
Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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 Mz. Alexa Mantena 4 Mz. Shui Qui 5 Mr. William Hsu 6 Mz. Danielle Stewart 7 Mz. Martha Mcgrath 8 Mr. Henry Fayol 9 Mr. Dick Watson 10 Mz. 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
Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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 Kết luận Ở phần một này chúng ta đ ã thấy một số ví dụ minh hoạ các cách sử dụng hàm CASE đơn giản trong truy vấn SQL. Trong phần 2 chúng ta sẽ tiếp tục thảo luận cách sử dụng hàm CASE ở những trường hợp phức tạp.
Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com Hàm CASE trong SQL Server 2005 (Phần 2) Trong phần 1 của loạt bài này chúng tôi đã giải thích các sử dụng hàm CASE đơn giản trong truy vấn. Trong phần II này, chúng tôi sẽ tiếp tục thảo luận về cách sử dụng hàm CASE trong một số trường hợp khác. 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)
Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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
Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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)
Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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:
Simpo PDF Merge and Split Unregistered Version - http://www.simpopdf.com 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 Achieve Top Results Priscilla A. Glidden NC Kết luận Trong phần một và phần hai của loạt bài này, chúng tôi đã hướng dẫn cách sử dụng các hàm CASE trong SQL Server. Trong phần tiếp theo, chúng ta sẽ xem xét cách sử dụng hàm CASE trong mệnh đề GROUP BY