Xử Lý Bài Toán Phức Tạp Với Subquery & Bảng Tạm CTE

Chào mừng bạn tiếp tục hành trình trong chuỗi series tự học SQL cùng MCNA Technology School!

Ở các buổi học trước, bạn đã biết cách lọc, gom nhóm và kết nối các bảng. Tuy nhiên, khi gặp những bài toán kinh doanh phức tạp như: “Tìm danh sách những học viên có điểm cao hơn điểm trung bình toàn trường”, bạn không thể giải quyết chỉ bằng một câu lệnh SELECT đơn lẻ.

Bài viết này sẽ hướng dẫn bạn cách sử dụng Subquery và CTE trong SQL để chia nhỏ và xử lý trơn tru mọi truy vấn lồng nhau.

1. Subquery (Truy Vấn Con) Là Gì?

Subquery là một câu lệnh SELECT được đặt bên trong một câu lệnh SQL khác (nằm trong dấu ngoặc đơn ()). Kết quả của Subquery sẽ được dùng làm đầu vào cho câu truy vấn chính.

Cú pháp cơ bản:

SELECT các_cột
FROM tên_bảng
WHERE cột toán_tử (
    SELECT cột 
    FROM tên_bảng 
    WHERE điều_kiện
);

Ví dụ thực tế:

Tìm danh sách học viên có điểm thi cao hơn điểm trung bình của toàn trường tại MCNA:

SELECT HoTen, DiemThi
FROM HocVien
WHERE DiemThi > (
    SELECT AVG(DiemThi) 
    FROM HocVien
);

(Trong ví dụ trên, Subquery (SELECT AVG(DiemThi) FROM HocVien) sẽ chạy trước để tính ra con số điểm trung bình cụ thể, sau đó câu lệnh chính mới lọc các học viên có điểm cao hơn con số đó).

2. CTE (Common Table Expression) – Bảng Tạm Tối Ưu Code

Khi các câu Subquery lồng nhau quá nhiều cấp, code SQL sẽ trở nên vô cùng rắc rối và khó đọc. Lúc này, CTE (Common Table Expression) ra đời như một giải pháp giúp bạn định nghĩa một “bảng tạm thời” bằng từ khóa WITH.

Cú pháp cơ bản:

WITH Ten_Bang_Tam AS (
    -- Câu lệnh truy vấn tạo bảng tạm
    SELECT các_cột 
    FROM tên_bảng
)
-- Câu truy vấn chính sử dụng bảng tạm
SELECT * 
FROM Ten_Bang_Tam;

Ví dụ thực tế:

Thực hiện lại bài toán tìm học viên có điểm hơn mức trung bình nhưng bằng cú pháp CTE:

WITH DiemTrungBinhTruong AS (
    SELECT AVG(DiemThi) AS DiemTB 
    FROM HocVien
)
SELECT hv.HoTen, hv.DiemThi
FROM HocVien hv, DiemTrungBinhTruong dtb
WHERE hv.DiemThi > dtb.DiemTB;

3. Bảng So Sánh Subquery Và CTE trong SQL

4. Bài Tập Thực Hành Học SQL

Hãy vận dụng Subquery và CTE trong SQL để giải quyết bài tập phân tích doanh thu dưới đây:

Đề bài: Cho bảng NhanVien (MaNV, HoTen, PhongBan, Luong).

1. Viết câu lệnh tìm các nhân viên có mức lương cao nhất trong công ty (Dùng Subquery).

2. Viết câu lệnh tạo một CTE tính lương trung bình từng phòng ban, sau đó hiển thị các phòng ban có lương trung bình trên 15,000,000 (Dùng CTE).

-- Câu 1: Dùng Subquery tìm nhân viên lương cao nhất
SELECT HoTen, Luong
FROM NhanVien
WHERE Luong = (
    SELECT MAX(Luong) 
    FROM NhanVien
);

-- Câu 2: Dùng CTE lọc phòng ban có lương trung bình > 15,000,000
WITH LuongTB_PhongBan AS (
    SELECT PhongBan, AVG(Luong) AS LuongTB
    FROM NhanVien
    GROUP BY PhongBan
)
SELECT PhongBan, LuongTB
FROM LuongTB_PhongBan
WHERE LuongTB > 15000000;