Trong bài viết này, chúng ta sẽ cùng tìm hiểu về các bảng dẫn xuất của MySQL và cách sử dụng chúng để đơn giản hóa các truy vấn phức tạp.

Giới thiệu về bảng dẫn xuất MySQL

Bảng dẫn xuất là một bảng ảo được trả về từ một câu lênh SELECT. Bảng dẫn xuất tương tự như bảng tạm thời, nhưng việc sử dụng bảng dẫn xuất trong câu lệnh SELECT đơn giản hơn nhiều so với bảng tạm thời vì nó không yêu cầu tạo bảng tạm thời.

Thuật ngữ bảng dẫn xuất và truy vấn con thường được sử dụng thay thế cho nhau. Khi một truy vấn con độc lập được sử dụng trong mệnh đề FROM của câu lệnh SELECT, nó còn được gọi là bảng dẫn xuất.

Sau đây minh họa một truy vấn sử dụng bảng dẫn xuất:

Lưu ý rằng truy vấn con độc lập là truy vấn con có thể thực thi độc lập với truy vấn bên ngoài.

Không giống như truy vấn con, bảng dẫn xuất phải có bí danh để bạn có thể tham chiếu tên của nó sau này trong truy vấn. Nếu một bảng dẫn xuất không có bí danh, MySQL sẽ đưa ra lỗi sau:

Every derived table must have its own alias.

Sau đây minh họa cú pháp của truy vấn sử dụng bảng dẫn xuất:

SELECT 
    select_list
FROM
    (SELECT 
        select_list
    FROM
        table_1) derived_table_name
WHERE 
    derived_table_name.c1 > 0;

Một ví dụ về bảng dẫn xuất MySQL đơn giản

Truy vấn sau đây lấy năm sản phẩm đầu tiên theo doanh thu bán hàng trong năm 2003 từ các bảng orders và orderdetails trong cơ sở dữ liệu mẫu:

SELECT 
    productCode, 
    ROUND(SUM(quantityOrdered * priceEach)) sales
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2003
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;

kết quả

Bạn có thể sử dụng kết quả của truy vấn này làm bảng dẫn xuất và nối nó với bảng products như sau:

SELECT 
    productName, sales
FROM
    (SELECT 
        productCode, 
        ROUND(SUM(quantityOrdered * priceEach)) sales
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY productCode
    ORDER BY sales DESC
    LIMIT 5) top5products2003
INNER JOIN
    products USING (productCode);

Phần sau đây cho thấy đầu ra của truy vấn trên:

Trong ví dụ này:

1. Đầu tiên, truy vấn con được thực thi để tạo tập kết quả hoặc bảng dẫn xuất.
2. Sau đó, truy vấn bên ngoài được thực hiện để nối bảng dẫn suất top5product2003 với bảng products bằng cách sử dụng cột productCode.

Một ví dụ về bảng dẫn xuất MySQL phức tạp hơn

Giả sử bạn phải phân loại những khách hàng đã mua sản phẩm trong năm 2003 thành 3 nhóm: platinum, gold, và silver. Và bạn cần biết số lượng khách hàng trong mỗi nhóm với các điều kiện sau:

• Khách hàng bạch kim có đơn hàng lớn hơn 100K.
• Khách hàng vàng có đơn hàng với khối lượng từ 10K đến 100K.
• Những khách hàng Bạc có đơn hàng với khối lượng dưới 10K.

Để tạo truy vấn này, trước tiên bạn cần đặt từng khách hàng vào nhóm tương ứng bằng cách sử dụng biể thức CASE và mệnh đề GROUP BY như sau:

SELECT 
    customerNumber,
    ROUND(SUM(quantityOrdered * priceEach)) sales,
    (CASE
        WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
        WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
        WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
    END) customerGroup
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2003
GROUP BY customerNumber;

Sau đây là đầu ra của truy vấn:

Sau đó, bạn có thể sử dụng truy vấn này làm bảng dẫn xuất và thực hiện nhóm như sau:

SELECT 
    customerGroup, 
    COUNT(cg.customerGroup) AS groupCount
FROM
    (SELECT 
        customerNumber,
            ROUND(SUM(quantityOrdered * priceEach)) sales,
            (CASE
                WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
                WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
                WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
            END) customerGroup
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;

Truy vấn trả về các nhóm khách hàng và số lượng khách hàng trong mỗi nhóm.

Tổng kết

Trong hướng dẫn này, bạn đã học cách sử dụng các bảng dẫn xuất của MySQL là các truy vấn con trong mệnh đề FROM để đơn giản hóa các truy vấn phức tạp.

Các bạn có thể tham khảo: https://www.mysqltutorial.org/mysql-derived-table/

Các bài viết liên quan:

• Mysql inner join
• Mysql left join
• Mysql Group By
• MySQL HAVING
• MySQL Subquery