Sử dụng CTE (Common Table Expression) trong sql server môn Công nghệ thông tin | Trường đại học kinh doanh và công nghệ Hà Nội

lOMoAR cPSD| 45469857 1. CTE
[SQLSERVER] Sử dụng CTE (Common Table Expression) trong sql server
Bài viết hôm nay, mình sẽ hướng dẫn các bạn sử dụng Common Table
Expression được viết tắt là CTE trong sql server. Vậy CTE trong sqlserver là
gì? CTE có thể được xem như một bảng chứa dữ liệu tạm thời từ câu lệnh
được định nghĩa trong phạm ...
Bài viết hôm nay, mình sẽ hướng dẫn các bạn sử dụng Common Table
Expression được viết tắt là CTE trong sql server.
Vậy CTE trong sqlserver là gì?
CTE có thể được xem như một bảng chứa dữ liệu tạm thời từ câu lệnh được định
nghĩa trong phạm vi của nó. CTE tương tự như một bảng dẫn xuất (derived table)
ở chỗ nó không được lưu trữ như một đối tượng và chỉ kéo dài trong suốt thời gian
của câu truy vấn. Không giống như bảng dẫn xuất, CTE có thể tự tham chiếu tới
bản thân của nó và có thể tham chiếu nhiều lần trong một câu truy vấn.
*) Mục đích của CTE :
- Tạo truy vấn đệ quy (recursive query ).
- Thay thế View trong một số trường hợp.
- Cho phép nhóm một cột từ truy vấn con. lOMoAR cPSD| 45469857
- Tham chiếu tới bảng kết quả nhiều lần trong cùng một lệnh.
*) Ưu điểm của CTE :
CTE có nhiều ưu điểm như khả năng đọc dữ liệu được cải thiện và dễ dàng bảo trì
các truy vấn phức tạp. Các truy vấn có thể được phân thành các khối nhỏ, đơn
giản. Những khối này được sử dụng để xây dựng các CTE phức tạp hơn cho đến
khi tập hợp kết quả cuối cùng được tạo ra.
CTE có thể được định nghĩa trong function, store procedure, view, trigger.
*) Cú pháp của CTE :
Ví dụ sử dụng CTE trong sqlserver: USE AdventureWorks2008R2; GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) AS -- Define the CTE query. (
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL lOMoAR cPSD| 45469857 )
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear; GO
2. Gộp kết quả với UNION
Trước tiên bạn xem cú pháp của lệnh này đã nhé. SELECT statement1 UNION SELECT statement2
UNION có tác dụng gộp hai lệnh SELECT và loại bỏ đi những dòng bị trùng lặp
chỉ để lại một dòng duy nhất, tuy nhiên không phải lệnh nào cũng gộp được mà phải
thỏa mãn điều kiện sau: •
Tên của các column phải giống nhau •
Thứ tự các column phải giống nhau •
Tổng các column phải bằng nhau
3. Gộp kết quả với UNION ALL
Có cú pháp và tác dụng tương tự như UNION, chỉ có điều là lệnh UNION ALL sẽ
giữ lại tất cả dữ liệu kể cả những dòng bị trùng lặp. Cú pháp như sau: SELECT statement1 UNION ALL lOMoAR cPSD| 45469857 SELECT statement2
*) Ví dụ với UNION và UNION ALL Giả
sử mình có bảng CSDL như sau: Bảng EMPLOYEE1 1 ID NAME AGE ADDRESS 2 1 Cường 32 ĐăkLăk 3 2 Kính 25 Gia Lai 4 3 Chính 23 Kontum 5 4 Quyền 25 ĐăkLăk 6 5 Kim 27 Gia Lai 7 6 Tình 22 Bình Phước Bảng EMPLOYEE2 1 ID EMPLOYEE_ID TASK 2 1 Cường Tiếp Tân 3 2 Kính Dọn Dẹp 4 3 Chính Đặt Phòng 5 4 Quyền Nấu Ăn
Bây giờ mình thực hiện lấy danh sách tất cả nhân viên cua cả hai table trên và gộp
vào một kết quả duy nhất, kết quả chỉ trả về NAME. UNION CODE SELECT NAME FROM EMPLOYEE1 UNION SELECT NAME FROM EMPLOYEE2
Kết quả sẽ trả về 1 Cường lOMoAR cPSD| 45469857 2 Kính 3 Chính 4 Quyền 5 Kim 6 Tình
Kết quả trả vê đã lọc đi dữ liệu bị trùng. UNION ALL Code SELECT NAME FROM EMPLOYEE1 UNION ALL SELECT NAME FROM EMPLOYEE2 Kết quả 1 Cường 2 Kính 3 Chính 4 Quyền 5 Kim 6 Tình 7 Cường 8 Kính 9 Chính 10 Quyền
Kết quả trả về vẫn giữ nguyên như cũ.
Như vậy lệnh UNION và UNION đều có công dụng là gộp kết quả từ hai câu lệnh
select, nhưng có điểm khác biệt là UNION sẽ loại bỏ dữ liệu trùng lặp còn UNION
ALL thì giữ lại tất cả. lOMoAR cPSD| 45469857
Trường hợp tên row không giống nhau thì bạn sử dụng từ khóa AS để đổi tên cho trùng khớp.
4. Toán tử EXCEPT trong SQL Server
Toán tử EXCEPT trong SQL Server được dùng để trả về các hàng trong lệnh
SELECT đầu tiên mà không trả về trong lệnh SELECT thứ hai. Mỗi lệnh SELECT
sẽ có một bộ dữ liệu. Toán tử EXCEPT lấy bản ghi từ bộ thứ 1 và bỏ các kết quả từ bộ 2. Truy vấn EXCEPT
Giải thích: Truy vấn EXCEPT trả về bản ghi trong khu vực màu xanh, chỉ nằm
trong bộ dữ liệu 1 và không nằm trong bộ dữ liệu 2.
Mỗi lệnh SELECT trong truy vấn EXCEPT phải có cùng số trường trong bộ kết
quả với kiểu dữ liệu giống nhau.
Cú pháp toán tử EXCEPT
SELECT bieu_thuc1, bieu_thuc2, … bieu_thucn FROM bang [ WHERE dieu_kien ] EXCEPT
SELECT bieu_thuc1, bieu_thuc2, … bieu_thucn FROM bang [ WHERE dieu_kien]; lOMoAR cPSD| 45469857
Tên biến hoặc giá trị biến bieu_thuc
Cột hoặc giá trị mà bạn muốn so sánh giữa 2 lệnh SELECT. Chúng không nhất thiết
phải nằm trong cùng 1 trường thông tin ở mỗi lệnh SELECT nhưng các cột tương
ứng phải có dữ liệu giống nhau. bang
Bảng muốn lấy bản ghi từ đó. Phải có ít nhất 1 bảng trong mệnh đề FROM. WHERE dieu_kien
Tùy chọn. Điều kiện phải đáp ứng để bản ghi được chọn. Lưu ý: •
Hai lệnh SELECT phải có cùng số biểu thức. •
Cột tương ứng trong mỗi lệnh SELECT phải có cùng kiểu dữ liệu. •
Toán tử EXCEPT trả về tất cả bản ghi từ lệnh SELECT đầu tiên và không nằm trong lệnh SELECT thứ 2. •
Toán tử EXCEPT trong SQL Server tương đương với toán tử MINUS trong Oracle.
Ví dụ - với 1 biểu thức SELECT sanpham_id FROM sanpham EXCEPT SELECT sanpham_id FROM hangtonkho;
Ở ví dụ với toán tử EXCEPT này, kết quả trả về tất cả các giá trị sanpham_id nằm
trong bảng sanpham và không nằm trong bảng hangtonkho. Nghĩa là nếu giá trị
sanpham_id nào có ở cả 2 bảng thì sẽ không được trả về.
Ví dụ - với nhiều biểu thức SELECT danhba_id, ho, ten lOMoAR cPSD| 45469857 FROM danhba WHERE ho = ‘Anderson’ EXCEPT SELECT nhanvien_id, ho, ten FROM nhanvien;
Ở ví dụ này, truy vấn trả về các bản ghi trong bảng danhba với ID của số liên lạc,
họ và tên không trùng với ID, họ và tên của nhân viên trong bảng nhanvien.
Ví dụ - dùng mệnh đề ORDER BY
SELECT nhacung_id, nhacung_ten FROM nhacung WHERE bang = ‘Florida’ EXCEPT SELECT congty_id, congty_ten FROM congty WHERE congty_id <= 400 ORDER BY 2;
Trong ví dụ này, do tên cột ở 2 lệnh SELECT khác nhau nên sẽ dễ hơn khi tham
chiếu tới cột bằng mệnh đề ORDER BY qua vị trí trong bộ kết quả. Ở ví dụ trên, ta
lọc kết quả nhacung_ten / congty_ten theo thứ tự tăng dần qua cụm từ ORDER BY 2.
Vì nhacung_ten / congty_ten đứng thứ 2 trong bộ kết quả.
5. Mệnh đề PIVOT trong SQL Server
Trong SQL Server ( Transact-SQL), mệnh đề PIVOT cho phép phân tích bảng chéo
(cross tabulation) chuyển dữ liệu từ bảng này sang bảng khác, tức là lấy kết quả
tổng hợp rồi chuyển từ dòng thành cột. lOMoAR cPSD| 45469857
Ví dụ tính tổng rồi chuyền hàng thành cột trong bảng dữ liệu
Cú pháp mệnh đề PIVOT SELECT cot_dautien AS ,
[ giatri_chuyen1], [giatri_chuyen2], … [giatri_chuyen_n ] FROM
() AS < bidanh_bang_nguon > PIVOT ( ham_tong () FOR < cot_chuyen >
IN ([giatri_chuyen1], [giatri_chuyen2], … [giatri_chuyen_n]) ) AS ;
Tên biến hoặc giá trị biến cot_dautien: Cột hoặc biểu thức sẽ thành cột đầu tiên
trong bảng mới sau khi chuyển. bidanh_cot_dautien: Tên của cột đầu tiên trong
bảng mới sau khi chuyển.
giatri_chuyen1, giatri_chuyen2, … giatri_chuyen_n: Danh sách các giá trị cần chuyển.
bang_nguon: Lệnh SELECT đưa dữ liệu nguồn (dữ liệu ban đầu) vào bảng mới.
bidanh_bang_nguon: Bí danh của bang_nguon ham_tong: Hàm tính tổng như
SUM trong SQL Server , COUNT, MIN, MAX hay AVG. cot_tong: Cột hoặc biểu
thức được dùng với ham_tong. cot_chuyen: Cột chứa giá trị cần chuyển.
bidanh_bang_chuyen: Bí danh của bảng sau khi chuyển. lOMoAR cPSD| 45469857
Mệnh đề PIVOT có thể dùng trong các phiên bản sau của SQL Server: SQL Server
2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.
Để thực hiện theo các bước trong hướng dẫn, hãy xem phần DDL để tạo bảng và
DML để tạo dữ liệu ở cuối bài viết này rồi thử chạy trên chính cơ sở dữ liệu của bạn.
Ví dụ với mệnh đề PIVOT
Ta có bảng nhanvien với các dữ liệu như dưới đây. so_nhanvien ho ten luong id_phong 12009 Nguyen Huong 54000 45 34974 Pham Hoa 80000 45 34987 Phan Lan 42000 45 45001 Tran Hua 57500 30 75623 Vu Hong 65000 30
Chạy lệnh SQL dưới đây để tạo truy vấn chéo bằng mệnh đề PIVOT.
SELECT ‘TongLuong’ AS TongLuongTheoPhong, [30] , [45] FROM ( SELECT id_phong, luong FROM nhanvien) AS BangNguon PIVOT ( SUM(luong) FOR id_phong IN ([30], [45]) ) AS BangChuyen;
Kết quả trả về sẽ như dưới đây. lOMoAR cPSD| 45469857 TongLuongTheoPhong 30 45 TongLuong 122500 176000
Ví dụ trên tạo một bảng sau khi đã chuyển dữ liệu, cho biết tổng lương của phòng
có ID là 30 và phòng có ID là 45. Kết quả nằm trên 1 hàng với 2 cột, mỗi cột là 1 phòng.
Xác định cụ thể cột trong bảng mới của truy vấn chéo
Trước tiên cần xác định trường thông tin nào muốn đưa vào bảng chuyển. Ở ví dụ
này là TongLuong làm cột đầu tiên, sau đó là 2 cột id_phong 30 và id_phong 45.
SELECT “TongLuong’ AS TongLuongTheoPhong,[30], [45]
Xác định dữ liệu trong bảng nguồn
Tiếp theo là xác định lệnh SELECT sẽ trả về dữ liệu nguồn cho bảng mới.
Ở ví dụ này là id_phong và luong từ bảng nhanvien. ( SELECT id_phong, luong FROM nhanvien) AS BangNguon
Cần chỉ ra bí danh cho truy vấn nguồn, trong ví dụ này là BangNguon.
Xác định hàm tính tổng
Hàm có thể dùng trong truy vấn chéo gồm SUM, COUNT, MIN, MAX và AVG.
Ở ví dụ này là hàm tính tổng SUM. PIVOT ( SUM(luong )
Xác định giá trị cần chuyển
Cuối cùng là giá trị cần chuyển để đưa vào kết quả. Đây sẽ là tiêu đề cột trong truy vấn chéo.
Ở ví dụ này, chúng ta chỉ cần trả về id_phòng 30 và 45. Các giá trị này sẽ là tên cột
trong bảng mới. Cần nhớ là những giá trị này là danh sách có giới hạn của các giá
trị id_phong và không nhất thiết phải chứa tất cả các giá trị. lOMoAR cPSD| 45469857 FOR id_phong IN ([30], [45]) II. BÀI TẬP
create database QuanLySinhVien; go use QuanLySinhVien; go create table SinhVien(
Masv nvarchar(50) not null primary key, Hosv nvarchar(50), Tensv nvarchar(50), Phai nvarchar(50), Ns datetime,
MaKhoa nvarchar(50), foreign key (MaKhoa)
references Khoa(Makhoa) ) ; go create table KetQua ( Masv nvarchar(50), Mamh nvarchar(50),
Lanthi int, Diem float, primary key (Masv,
Mamh, Lanthi), foreign key (Masv) references
SinhVien(Masv), foreign key (Mamh)
references MonHoc(Mamh) ) ; go create table Khoa ( lOMoAR cPSD| 45469857
Makhoa nvarchar(50) not null primary key, Tenkhoa nvarchar(50) ) ; go create table MonHoc ( Mamh nvarchar(50) primary key, Tenmh nvarchar(50), Sotiet int ) ; 1.
Thêm dữ liệu cho khoa CNTT thêm 5 sinh viên mới. Insert into sinhvien values () 2.
Chuyển những sinh viên khoa DTVT sang khoa CNTT. Update Set where 3.
Thay đổi tất cả các môn học 30 tiết lên 35 tiết. Update Set where 4.
Sửa toàn bộ điểm lần 2 của các sinh viên khoa CNTT thêm 0.5 điểm (lưu ý
điểm sau khi sửa không vượt quá 10). Update Set From where lOMoAR cPSD| 45469857 5.
Xóa những sinh viên của khoa Môi trường, xóa khoa Môi trường. Delete from where 6.
Đưa ra danh sách SV có tuổi trên 30 (CTE) 7.
Đưa ra danh sách những khoa có số lượng SV nhiều hơn số lượng SV trung
bình chung của mỗi khoa (CTE). 8. Xây dựng 2 CTE bất kỳ. 9.
Tạo bảng SinhVienTest có cấu trúc giống bảng SinhVien.
Nhập dữ liệu cho bảng SinhVienTest có bản ghi trùng bảng SV, có bản ghi
khác bảng SinhVien. Áp dụng truy vấn intersect, except, union, union all trên 2 bảng này.
10. Xây dựng 1 truy vấn except, 1 truy vấn union và union all bất kỳ.
11. Đưa ra số lượng SV của khoa DTVT, CNTT (dùng pivot).
12. Đưa ra số lượng SV nam, SV nữ (dùng pivot).
13. Xây dựng 1 truy vấn pivot bất kỳ.
14. Xây dựng 1 truy vấn rollup theo 3 trường trong 1 bảng.
15. Xây dựng 1 truy vấn rollup theo 3 trường lấy dữ liệu từ ít nhất 2 bảng.