Hầu hết các báo cáo đều cần một số logic: gắn nhãn các đơn hàng lớn hay nhỏ, đếm các đơn hàng đã thanh toán và đang chờ xử lý cạnh nhau hoặc hiển thị các mặt hàng khẩn cấp trước tiên. TRƯỜNG HỢP SQL KHI xử lý tất cả những điều này trực tiếp bên trong một truy vấn và nó hoạt động trên tất cả các cơ sở dữ liệu chính.

Chúng tôi đã chạy mọi truy vấn ở đây bằng SQLite 3.50 và kết quả hiển thị là từ lần chạy đó. CASE là một phần của SQL tiêu chuẩn, vì vậy nó hoạt động theo cách tương tự trong PostgreSQL, MySQL, SQL Server và Oracle đối với các ví dụ này.

TRƯỜNG HỢP KHI NÀO là gì?

CASE là một biểu thức, không phải là một lệnh riêng biệt. Nó đánh giá các điều kiện theo thứ tự và trả về một giá trị, do đó nó có thể đi đến bất cứ nơi nào mà giá trị được cho phép: trong SELECT, WHERE, GROUP BY, ORDER BY hoặc bên trong các hàm. Nó luôn kết thúc bằng END.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE fallback
END

Bảng mẫu

CREATE TABLE orders (id INT, customer TEXT, total INT, status TEXT);

INSERT INTO orders VALUES
 (1,'Asha',1200,'paid'), (2,'Ben',300,'pending'),
 (3,'Asha',800,'paid'), (4,'Chitra',2500,'shipped'),
 (5,'Dev',150,'cancelled'), (6,'Ben',NULL,'pending');

Lưu ý rằng lệnh 6 không có tổng. Hàng đó quan trọng sau này.

TRƯỜNG HỢP đã tìm kiếm

Đây là dạng linh hoạt hơn, vì mỗi WHEN có đầy đủ điều kiện riêng:

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 500 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders;

-- 1 | 1200 | large
-- 2 |  300 | small
-- 3 |  800 | medium
-- 4 | 2500 | large
-- 5 |  150 | small
-- 6 | NULL | small

Nhìn vào hàng cuối cùng. Đơn hàng 6 không có tổng nhưng lại được dán nhãn “nhỏ”. Cả hai so sánh với NULL đều không đúng, vì vậy nó rơi vào ELSE. Chúng tôi trình bày cách khắc phục điều này trong phần NULL. Luôn đặt tên cho kết quả bằng AS, nếu không bạn có thể nhận được một tên cột khó hiểu.

TRƯỜNG HỢP ĐƠN GIẢN

Khi bạn so sánh một cột với các giá trị cố định, biểu mẫu ngắn hơn sẽ đọc tốt hơn:

SELECT id,
       CASE status
         WHEN 'paid' THEN 'Complete'
         WHEN 'shipped' THEN 'Complete'
         WHEN 'pending' THEN 'Waiting'
       END AS label
FROM orders;

-- 1 | Complete
-- 2 | Waiting
-- 3 | Complete
-- 4 | Complete
-- 5 | NULL
-- 6 | Waiting

Vấn đề đặt hàng

CASE dừng ở điều kiện đúng đầu tiên và đây là lỗi logic phổ biến nhất. Hoán đổi hai điều kiện từ truy vấn trước đó của chúng tôi:

SELECT id, total,
       CASE
         WHEN total >= 500 THEN 'medium'
         WHEN total >= 1000 THEN 'large'
         ELSE 'small'
       END AS size
FROM orders WHERE id IN (1, 4);

-- 1 | 1200 | medium
-- 4 | 2500 | medium

Bây giờ không có gì có thể “lớn”. Tổng số 2500 cũng ít nhất là 500, nên nhánh đầu tiên thắng và nhánh thứ hai không bao giờ đạt được. Không có lỗi xuất hiện, chỉ có nhãn sai. Đối với các phạm vi, hãy đặt điều kiện hạn chế nhất trước tiên hoặc viết các phạm vi hoàn chỉnh, chẳng hạn như total >= 500 AND total < 1000.

Điều gì xảy ra nếu không có ELSE?

Trong ví dụ CASE đơn giản của chúng tôi ở trên, đơn hàng 5 đã bị hủy và không có chi nhánh nào xử lý việc đó, vì vậy kết quả là NULL. Đôi khi đó chính xác là điều bạn muốn, như phần tiếp theo sẽ trình bày. Nhưng khi tạo nhãn, ELSE 'other' rõ ràng thường tốt hơn vì nó hiển thị các giá trị không mong muốn thay vì để trống trong báo cáo.

Báo cáo kiểu tổng hợp với SUM

Đây là một trong những mẫu hữu ích nhất trong SQL hàng ngày. Việc gói CASE trong một tổng hợp sẽ biến các hàng thành các cột, với một hàng cho mỗi khách hàng:

SELECT customer,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer
ORDER BY customer;

-- Asha   | 2000 | 0
-- Ben    |    0 | 2
-- Chitra |    0 | 0
-- Dev    |    0 | 0

Một truy vấn hiện cung cấp cả doanh thu được thanh toán và số lượng đơn đặt hàng đang chờ xử lý mà không cần các truy vấn hoặc liên kết riêng biệt. Mẫu này hoạt động trong hầu hết mọi cơ sở dữ liệu, không giống như cú pháp PIVOT chuyên dụng mà chỉ một số cơ sở dữ liệu cung cấp. Nếu GROUP BY là điều mới mẻ đối với bạn, hãy đọc SQL GROUP BY and HAVING.

Số lượng có điều kiện

COUNT bỏ qua NULL, do đó CASE không có ELSE chỉ tính các hàng khớp:

SELECT COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN total >= 1000 THEN 1 END) AS large_orders
FROM orders;

-- 6 | 2 | 2

Hãy cẩn thận: nếu bạn thêm ELSE 0 vào trong COUNT, mọi hàng sẽ được tính vì 0 không phải là NULL. Sử dụng ELSE 0 với SUM và bỏ nó đi với COUNT. PostgreSQL và SQLite cũng hỗ trợ COUNT(*) FILTER (WHERE ...), đọc rất hay nhưng không phải ở đâu cũng có.

Nhóm theo CASE

Bạn có thể tạo nhóm của riêng mình rồi tóm tắt chúng:

SELECT CASE WHEN total >= 1000 THEN 'large' ELSE 'other' END AS bucket,
       COUNT(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;

-- large | 2
-- other | 4

Nhóm theo bí danh hoạt động trong SQLite, PostgreSQL và MySQL. SQL Server không cho phép điều đó, vì vậy trước tiên bạn sẽ phải lặp lại toàn bộ biểu thức hoặc đặt truy vấn vào CTE hoặc truy vấn con.

Sắp xếp tùy chỉnh với ORDER BY

Thứ tự bảng chữ cái hiếm khi là điều doanh nghiệp mong muốn. CASE cho phép bạn xác định thứ tự của riêng mình:

SELECT id, status FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'paid' THEN 2
           ELSE 3
         END, id;

-- 2 | pending
-- 6 | pending
-- 1 | paid
-- 3 | paid
-- 4 | shipped
-- 5 | cancelled

Các đơn đặt hàng đang chờ xử lý được ưu tiên trước, điều này rất hữu ích cho hàng đợi công việc. Cột sắp xếp thứ hai, id, giữ các hàng theo thứ tự có thể dự đoán được trong mỗi nhóm.

TRƯỜNG HỢP trong CẬP NHẬT

CASE cho phép một câu lệnh áp dụng các thay đổi khác nhau cho các hàng khác nhau. Tại đây, các lệnh nhỏ đang chờ xử lý được gắn cờ để xem xét:

UPDATE orders
SET status = CASE
               WHEN status = 'pending' AND total < 500 THEN 'review'
               ELSE status
             END;

SELECT id, status FROM orders WHERE customer = 'Ben';

-- 2 | review
-- 6 | pending

ELSE status giữ các hàng khác không thay đổi. Bỏ nó đi là nguy hiểm, vì các hàng không khớp sẽ được đặt thành NULL. Nếu không có mệnh đề WHERE thì câu lệnh này cũng chạm vào mọi hàng, do đó, trên các bảng lớn, hãy thêm bộ lọc và xem xét gói thay đổi vào giao dịch để bạn có thể kiểm tra kết quả trước khi thực hiện. Cũng lưu ý rằng thứ tự 6 không thay đổi, vì tổng giá trị NULL của nó không thỏa mãn total < 500.

Bẫy vô giá trị

NULL gây ra hai vấn đề riêng biệt với CASE. Đầu tiên, dạng đơn giản so sánh bằng cách sử dụng đẳng thức và không có gì bằng NULL, thậm chí chính nó cũng không bằng NULL:

SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'not matched' END;

-- not matched

Vì vậy CASE col WHEN NULL không bao giờ khớp. Thay vào đó, hãy sử dụng biểu mẫu tìm kiếm với IS NULL. Thứ hai, như chúng ta đã thấy với đơn hàng 6, giá trị NULL vượt qua các so sánh và đạt đến ELSE. Cách khắc phục là kiểm tra nó trước:

SELECT id,
       CASE
         WHEN total IS NULL THEN 'unknown'
         WHEN total >= 1000 THEN 'large'
         ELSE 'other'
       END AS size
FROM orders WHERE id IN (1, 2, 6);

-- 1 | large
-- 2 | other
-- 6 | unknown

Đặt hàng 6 bây giờ nhận được một nhãn trung thực. Hướng dẫn của chúng tôi về Các giá trị SQL NULL trình bày đầy đủ về hành vi này.

CASE so với các tùy chọn khác

Lựa chọn cuối cùng đó xứng đáng được suy nghĩ. Nếu một CASE phát triển thành ba mươi nhánh và xuất hiện trong năm truy vấn, hãy di chuyển ánh xạ vào một bảng và nối vào bảng đó. Sau đó, thay đổi là cập nhật một hàng chứ không phải thay đổi mã ở năm vị trí.

Những lỗi thường gặp

• Thứ tự điều kiện sai. Điều kiện rộng trước tiên sẽ ẩn điều kiện hẹp hơn.
• Quên END. Đây là lỗi cú pháp thường gặp nhất.
• Sử dụng WHEN NULL. Nó không bao giờ khớp, vì vậy hãy sử dụng WHEN col IS NULL.
• Thêm ELSE 0 vào trong COUNT. Nó đếm tất cả các hàng.
• Bỏ qua ELSE trong CẬP NHẬT. Nó có thể xóa các giá trị bằng NULL.
• Lọc theo CASE trong WHERE. Nó có thể ngăn việc sử dụng một chỉ mục. Xem hướng dẫn của chúng tôi về Tối ưu hóa truy vấn SQL.

Lời kết

TRƯỜNG HỢP KHI NÀO là một tính năng nhỏ nhưng lại có tác dụng rất lớn. Hãy nhớ ba quy tắc: nhánh thực sự đầu tiên sẽ thắng, các hàng chưa khớp sẽ trở thành NULL nếu không có ELSE và NULL cần kiểm tra riêng. Để thực hành, hãy lấy một báo cáo mà bạn hiện đang xây dựng với một số truy vấn và cố gắng tạo báo cáo đó dưới dạng một truy vấn bằng cách sử dụng SUM(CASE ...).

Câu hỏi thường gặp

Tiếp tục học hỏi