Phép chia, gom nhóm và kết ngoài

01 · PHÉP CHIA TRONG SQL

Phép chia là gì?

Từ khóa nhận diện

Khi đề bài có những cụm như tất cả, mọi, toàn bộ, "đã mua tất cả sản phẩm", "đã tham gia tất cả dự án", "đã đăng ký tất cả môn học" - Thì cần nghĩ ngay đến phép chia.

Ví dụ trực quan

Giả sử có 3 bảng:

Ta cần tìm: khách hàng đã mua tất cả sản phẩm.

Kết quả: KH01 - Hàng duy nhất có đủ dấu ✓ ở mọi cột sản phẩm. Đây chính là tư duy của phép chia.

Tư duy cốt lõi

Bài toán "Tìm A đã thực hiện tất cả B" có thể hiểu thành:

Trong SQL, tư duy này thường được triển khai bằng GROUP BY + HAVING COUNT(DISTINCT ...).

02 · PHÉP CHIA CƠ BẢN

Phép chia cơ bản

Mô hình: R(A, B, C, D, E) và S(D, E). Phép chia R / S tìm những giá trị A, B, C của R sao cho chúng liên kết với tất cả các giá trị D, E trong S.

Công thức GROUP BY + HAVING

SELECT B1.<Tên cột 1>, ...
FROM <Tên bảng 1> B1
JOIN <Tên bảng 3> B3
    ON B1.<Tên cột 1> = B3.<Tên cột 1>
GROUP BY B1.<Tên cột 1>
HAVING COUNT(DISTINCT B3.<Tên cột 2>) =
(
    SELECT COUNT(DISTINCT B2.<Tên cột 2>)
    FROM <Tên bảng 2> B2
);

Ví dụ 1 - Khách hàng mua tất cả sản phẩm

Bảng: KhachHang(MaKH, TenKH), SanPham(MaSP, TenSP), MuaHang(MaKH, MaSP).

SELECT KH.MaKH
FROM KhachHang KH
JOIN MuaHang MH
    ON KH.MaKH = MH.MaKH
GROUP BY KH.MaKH
HAVING COUNT(DISTINCT MH.MaSP) =
(
    SELECT COUNT(*)
    FROM SanPham
);

Phân tích câu SQL

Bước 1 - Kết nối khách hàng với lịch sử mua hàng: FROM KhachHang KH JOIN MuaHang MH ON KH.MaKH = MH.MaKH

Bước 2 - Gom theo từng khách hàng bằng GROUP BY KH.MaKH: KH01 ➜ SP01,SP02,SP03, KH02 ➜ SP01,SP02, KH03 ➜ SP01,SP03.

Bước 3 - Đếm số sản phẩm khác nhau đã mua bằng COUNT(DISTINCT MH.MaSP): KH01 ➜ 3, KH02 ➜ 2, KH03 ➜ 2.

Bước 4 - Đếm tổng số sản phẩm: SELECT COUNT(*) FROM SanPham ➜ 3.

Bước 5 - So sánh: Số SP khách hàng đã mua = tổng số SP tồn tại ➜ Khách hàng đã mua tất cả.

Ví dụ 2 - Sinh viên đăng ký tất cả môn bắt buộc

Bảng: SinhVien(MaSV, TenSV), MonHoc(MaMH, TenMH), DangKy(MaSV, MaMH).

SELECT SV.MaSV
FROM SinhVien SV
JOIN DangKy DK
    ON SV.MaSV = DK.MaSV
GROUP BY SV.MaSV
HAVING COUNT(DISTINCT DK.MaMH) =
(
    SELECT COUNT(*)
    FROM MonHoc
);

Sinh viên A đăng ký 5 môn, tổng môn bắt buộc = 5 ➜ Đạt. Sinh viên B đăng ký 4 môn, tổng môn bắt buộc = 5 ➜ Không đạt.

Ví dụ 3 - Nhân viên tham gia tất cả dự án

Bảng: NhanVien(MaNV, TenNV), DuAn(MaDA, TenDA), ThamGia(MaNV, MaDA).

SELECT NV.MaNV
FROM NhanVien NV
JOIN ThamGia TG
    ON NV.MaNV = TG.MaNV
GROUP BY NV.MaNV
HAVING COUNT(DISTINCT TG.MaDA) =
(
    SELECT COUNT(*)
    FROM DuAn
);

03 · PHÉP CHIA CÓ ĐIỀU KIỆN

Phép chia có điều kiện

Ví dụ: "Tìm khách hàng đã mua tất cả sản phẩm thuộc danh mục Điện tử" - Không còn là "tất cả sản phẩm" mà là "tất cả sản phẩm WHERE DanhMuc = 'Điện tử'".

Tập cần kiểm tra chỉ là SP01, SP02, chứ không phải cả 4 sản phẩm.

SELECT KH.MaKH
FROM KhachHang KH
JOIN MuaHang MH
    ON KH.MaKH = MH.MaKH
JOIN SanPham SP
    ON MH.MaSP = SP.MaSP
WHERE SP.DanhMuc = N'Điện tử'
GROUP BY KH.MaKH
HAVING COUNT(DISTINCT MH.MaSP) =
(
    SELECT COUNT(*)
    FROM SanPham
    WHERE DanhMuc = N'Điện tử'
);

Công thức tư duy

Ví dụ - Môn học bắt buộc trên 3 tín chỉ

Đề bài: "Tìm sinh viên đã đăng ký tất cả các môn học bắt buộc có số tín chỉ lớn hơn 3." Trước hết xác định tập môn cần xét bằng WHERE SoTinChi > 3, sau đó kiểm tra mỗi sinh viên có đăng ký đủ toàn bộ tập này hay không.

SELECT SV.MaSV
FROM SinhVien SV
JOIN DangKy DK
    ON SV.MaSV = DK.MaSV
JOIN MonHoc MH
    ON MH.MaMH = DK.MaMH
WHERE MH.SoTinChi > 3
GROUP BY SV.MaSV
HAVING COUNT(DISTINCT DK.MaMH) =
(
    SELECT COUNT(*)
    FROM MonHoc
    WHERE SoTinChi > 3
);

04 · PHÉP CHIA VỚI NOT EXISTS

Phép chia với NOT EXISTS

Đây là cách thứ hai để giải bài toán "tất cả" - Dùng điều kiện phủ định để kiểm tra xem có tồn tại giá trị nào không thỏa mãn hay không.

Tư duy "Tất cả" ➜ "Không tồn tại trường hợp thiếu"

"Khách hàng đã mua tất cả sản phẩm điện tử" có thể chuyển thành "Không tồn tại sản phẩm điện tử nào mà khách hàng chưa mua".

Cấu trúc hai lớp NOT EXISTS

SELECT ...
FROM A
WHERE NOT EXISTS
(
    SELECT *
    FROM B
    WHERE NOT EXISTS
    (
        SELECT *
        FROM C
        WHERE ...
    )
);

Ví dụ khách hàng

Đề bài: "Tìm khách hàng đã mua tất cả sản phẩm thuộc danh mục Điện tử."

SELECT KH.MaKH
FROM KhachHang KH
WHERE NOT EXISTS
(
    SELECT *
    FROM SanPham SP
    WHERE SP.DanhMuc = N'Điện tử'
      AND NOT EXISTS
      (
          SELECT *
          FROM MuaHang MH
          WHERE MH.MaKH = KH.MaKH
            AND MH.MaSP = SP.MaSP
      )
);

Đọc câu SQL bằng tiếng Việt

Đừng đọc SQL theo từng dòng. Hãy đọc: "Lấy những khách hàng mà không tồn tại sản phẩm điện tử nào mà khách hàng đó chưa mua." Nếu không có sản phẩm nào như vậy ➜ Khách hàng đã mua tất cả.

Ví dụ sinh viên

Đề bài: "Tìm sinh viên đã đăng ký tất cả môn học bắt buộc có số tín chỉ > 3."

SELECT SV.MaSV
FROM SinhVien SV
WHERE NOT EXISTS
(
    SELECT *
    FROM MonHoc MH
    WHERE MH.SoTinChi > 3
      AND NOT EXISTS
      (
          SELECT *
          FROM DangKy DK
          WHERE DK.MaMH = MH.MaMH
            AND DK.MaSV = SV.MaSV
      )
);

Ba cách nhận diện phép chia

05 · HÀM TẬP HỢP VÀ GOM NHÓM

Aggregate Functions

Hàm tập hợp cho phép thực hiện phép tính trên một tập nhiều dòng.

Phân biệt 3 loại COUNT

SELECT COUNT(*) FROM NhanVien; ➜ 5 (đếm tất cả dòng)

SELECT COUNT(Luong) FROM NhanVien; ➜ 4 (NV03 có Luong = NULL)

SELECT COUNT(DISTINCT ChucVu) FROM NhanVien; ➜ 2 (Dev, Tester - NULL không được tính)

SUM, AVG, MAX, MIN

SELECT SUM(Luong) AS TongLuong
FROM NhanVien;

SELECT AVG(Luong) AS LuongTrungBinh
FROM NhanVien;

Lưu ý: AVG bỏ qua giá trị NULL.

SELECT
    MAX(Luong) AS LuongCaoNhat,
    MIN(Luong) AS LuongThapNhat
FROM NhanVien;

GROUP BY hoạt động như thế nào?

Ví dụ dữ liệu:

GROUP BY Phong chia thành nhóm P01: NV01, NV02 và P02: NV03, NV04, sau đó mới thực hiện MAX/MIN/AVG/SUM/COUNT trên từng nhóm.

SELECT
    Phong,
    MAX(Luong) AS LuongCaoNhat,
    MIN(Luong) AS LuongThapNhat,
    AVG(Luong) AS LuongTrungBinh
FROM NhanVien
GROUP BY Phong;

Thống kê số nhân viên theo phòng

LEFT JOIN giúp vẫn hiển thị phòng ban ngay cả khi phòng đó chưa có nhân viên:

SELECT
    PB.MaPH,
    PB.TenPH,
    COUNT(NV.MaNV) AS SLNV
FROM PHONGBAN PB
LEFT JOIN NHANVIEN NV
    ON NV.Phong = PB.MaPH
GROUP BY
    PB.MaPH,
    PB.TenPH;

HAVING - Lọc nhóm

Đề bài: "Tìm phòng ban có số lượng nhân viên lớn hơn 10, hiển thị mã phòng, tên phòng và số lượng nhân viên, sắp xếp giảm dần."

SELECT
    PB.MaPH,
    PB.TenPH,
    COUNT(NV.MaNV) AS SLNV
FROM PHONGBAN PB
LEFT JOIN NHANVIEN NV
    ON NV.Phong = PB.MaPH
GROUP BY
    PB.MaPH,
    PB.TenPH
HAVING COUNT(NV.MaNV) > 10
ORDER BY
    COUNT(NV.MaNV) DESC;

WHERE và HAVING - Phải phân biệt

WHERE lọc dòng trước khi gom nhóm (WHERE Luong > 10000000). HAVING lọc nhóm sau khi gom nhóm (HAVING COUNT(*) > 10).

06 · SELECT TOP

SELECT TOP

TOP dùng để giới hạn số lượng dòng trả về.

SELECT TOP 4
    HoTen,
    Luong
FROM NhanVien;

TOP + ORDER BY

Nếu muốn lấy 4 nhân viên có lương cao nhất:

SELECT TOP 4
    HoTen,
    Luong
FROM NhanVien
ORDER BY Luong DESC;

TOP ... WITH TIES

Nếu người thứ 4 có mức lương 15 triệu và có thêm người khác cũng nhận 15 triệu, WITH TIES sẽ lấy cả những người đó:

SELECT TOP 4 WITH TIES
    HoTen,
    Luong
FROM NhanVien
ORDER BY Luong DESC;

DISTINCT TOP

Lấy 4 chức vụ khác nhau đầu tiên:

SELECT DISTINCT TOP 4
    ChucVu
FROM NhanVien;

TOP + GROUP BY + HAVING

Đề bài: "Lấy 4 phòng ban có tổng quỹ lương lớn nhất, chỉ xét phòng có tổng lương trên 5 triệu."

SELECT TOP 4
    MaPB,
    SUM(Luong) AS TongLuong
FROM NhanVien
GROUP BY MaPB
HAVING SUM(Luong) > 5000000
ORDER BY SUM(Luong) DESC;

07 · PHÉP KẾT NGOÀI

Phép kết ngoài

INNER JOIN chỉ trả về những dòng có dữ liệu khớp. Nhưng đôi khi cần biết cả những đối tượng không có dữ liệu liên quan - Ví dụ "cho biết những nhân viên không tham gia đề án nào." Nếu dùng INNER JOIN, nhân viên không tham gia đề án sẽ bị loại vì không có bản ghi tương ứng. Do đó cần LEFT JOIN.

Tìm nhân viên không tham gia đề án

SELECT
    NV.MaNV,
    NV.HoTen
FROM NHANVIEN NV
LEFT OUTER JOIN PHANCONG PC
    ON NV.MaNV = PC.MaNV
WHERE PC.MaDA IS NULL;

Ba loại OUTER JOIN

08 · BẢNG SO SÁNH NHANH

SQL Week 3 Cheat Sheet

Đọc đề ➜ Nhận diện

Tổng kết Tuần 3

Tuần 1 là thiết kế & thao tác dữ liệu, Tuần 2 là truy vấn & kết hợp dữ liệu, Tuần 3 là truy vấn nâng cao: Phép chia ("tất cả" ➜ GROUP BY+HAVING COUNT hoặc NOT EXISTS×2), gom nhóm (Aggregate, TOP/HAVING), và kết ngoài ("không có" ➜ LEFT JOIN+IS NULL).

09 · BÀI TẬP THỰC HÀNH

Thực hành tổng hợp

Ngoài câu 26-53 theo tài liệu chính thức, đây là các bài tự luyện mở rộng để hiểu sâu hơn tư duy Tuần 3 - Không thuộc bài nộp BTTH3. Mỗi bài có gợi ý ẩn - Bấm để mở.

Đây là bài rất tốt để hiểu rằng một bài toán SQL có thể có nhiều cách biểu diễn.