Trong bài viết này, bạn sẽ học cách sử dụng MySQL GROUP BY để nhóm các hàng thành các nhóm con dựa trên giá trị của các cột hoặc biểu thức.
Mệnh đề GROUP BY sẽ nhóm một tập hợp các hàng thành một tập hợp các hàng tóm tắt theo giá trị của các cột hoặc biểu thức. Mệnh đệ GROUP BY trả về một hàng cho mỗi nhóm. Nói cách khác, nó làm giảm số lượng hàng trong tập hợp kết quả.
Mệnh đề GROUP BY là mệnh đề tùy chọn của SELECT câu lệnh. Sau đây là cú pháp của mệnh đề GROUP BY:
SELECT
c1, c2,..., cn, aggregate_function(ci)
FROM
table
WHERE
where_conditions
GROUP BY c1 , c2,...,cn;
Trong cú pháp này, bạn đặt mệnh đề GROUP BY sau mệnh đề FROM và WHERE. Sau các từ khóa GROUP BY, bạn đặt danh sách các cột hoặc biểu thức được phân tách bằng dấu phẩy để nhóm các hàng.
MySQL đánh giá mệnh đề GROUP BY sau mệnh đề FROM và WHERE và trước mệnh đề HAVING, SELECT, DISTINCT, ORDER BY và LIMIT:
Thực tế thì bạn thường sử dụng GROUP BY với aggregate functions như SUM, AVG, MAX, MINvà COUNT. Hàm tổng hợp xuất hiện trong mệnh đề SELECT cung cấp thông tin của từng nhóm.
Cùng lấy một số ví dụ về việc sử dụng mệnh đề GROUP BY.
Hãy xem bảng orders trong cơ sở dữ liệu mẫu .
Giả sử bạn muốn nhóm các giá trị của trạng thái đơn hàng thành các nhóm con, bạn sử dụng mệnh đề GROUP BY với cột status như truy vấn sau:
SELECT
status
FROM
orders
GROUP BY status;
Kết quả:
Như bạn thấy từ tập kết quả, mệnh đề GROUP BY đề trả về status các giá trị xuất hiện duy nhất. Nó hoạt động giống như toán tử DISTINCT được hiển thị trong truy vấn sau:
SELECT DISTINCT
status
FROM
orders;
Các hàm tổng hợp cho phép bạn thực hiện tính toán một tập hợp các hàng và trả về một giá trị duy nhất. Mệnh đề GROUP BY thường được sử dụng với một hàm tổng hợp để thực hiện các phép tính và trả về một giá trị duy nhất cho mỗi nhóm con.
Ví dụ bạn muốn biết số lượng đơn hàng trong từng trạng thái thì có thể sử dụng hàm COUNT với mệnh đề GROUP BY như sau:
SELECT
status, COUNT(*)
FROM
orders
GROUP BY status;
Kết quả:
Xem phần sau bảng orders và bảng orderdetails.
Để lấy tổng tiền của tất cả các đơn hàng theo trạng thái, bạn JION bảng orders với bảng orderdetails và dùng hàm tính tổng tiền SUM. Xem truy vấn sau:
SELECT
status,
SUM(quantityOrdered * priceEach) AS amount
FROM
orders
INNER JOIN orderdetails
USING (orderNumber)
GROUP BY
status;
Kết quả:
Tương tự, truy vấn sau đây trả về số đơn đặt hàng và tổng số tiền của mỗi đơn đặt hàng.
SELECT
orderNumber,
SUM(quantityOrdered * priceEach) AS total
FROM
orderdetails
GROUP BY
orderNumber;
Kết quả:
Ngoài các cột, bạn có thể nhóm các hàng theo biểu thức. Truy vấn sau đây nhận được tổng doanh thu cho mỗi năm.
SELECT
YEAR(orderDate) AS year,
SUM(quantityOrdered * priceEach) AS total
FROM
orders
INNER JOIN orderdetails
USING (orderNumber)
WHERE
status = 'Shipped'
GROUP BY
YEAR(orderDate);
kết quả:
Trong ví dụ này, chúng tôi đã sử dụng hàm YEAR để trích xuất dữ liệu năm từ ngày đặt hàng ( orderDate). Chúng tôi chỉ bao gồm các đơn đặt hàng có trạng thái shipped trong tổng doanh số. Lưu ý rằng biểu thức xuất hiện trong mệnh đề SELECT phải giống với biểu thức trong mệnh đề GROUP BY.
Để lọc các nhóm được trả về bởi mệnh đề GROUP BY, bạn sử dụng một mệnh đề HAVING. Truy vấn sau sử dụng mệnh đề HAVING để chọn tổng doanh số của các năm sau năm 2003.
SELECT
YEAR(orderDate) AS year,
SUM(quantityOrdered * priceEach) AS total
FROM
orders
INNER JOIN orderdetails
USING (orderNumber)
WHERE
status = 'Shipped'
GROUP BY
year
HAVING
year > 2003;
kết quả:
Tiêu chuẩn SQL không cho phép bạn sử dụng bí danh (alias) trong mệnh đề GROUP BY trong khi MySQL hỗ trợ điều này.
Ví dụ: truy vấn sau trích xuất năm từ ngày đặt hàng. Trước tiên, nó sử dụng bí danh year của biểu thức YEAR(orderDate)và sau đó sử dụng bí danh year trong mệnh đề GROUP BY.
Truy vấn sau đây không hợp lệ trong tiêu chuẩn SQL:
SELECT
YEAR(orderDate) AS year,
COUNT(orderNumber)
FROM
orders
GROUP BY
year;
kết quả:
Ngoài ra, MySQL cho phép bạn sắp xếp các nhóm theo thứ tự tăng dần hoặc giảm dần. Thứ tự sắp xếp mặc định là tăng dần. Ví dụ: nếu bạn muốn lấy số lượng đơn đặt hàng theo trạng thái và sắp xếp trạng thái theo thứ tự giảm dần, bạn có thể sử dụng mệnh đề GROUP BY with DESC làm truy vấn sau:
SELECT
status,
COUNT(*)
FROM
orders
GROUP BY
status DESC;
kết quả:
Lưu ý DESC trong mệnh đề GROUP BY sắp xếp status theo thứ tự giảm dần. Và bạn cũng có thể sử dụng ASC rõ ràng trong mệnh đề GROUP BY để sắp xếp các nhóm theo trạng thái theo thứ tự tăng dần.
Nếu bạn sử dụng mệnh đề GROUP BY trong câu lệnh SELECT mà không sử dụng các hàm tổng hợp , thì mệnh đề GROUP BY đó sẽ hoạt động giống như DISTINCT mệnh đề.
Câu lệnh sau sử dụng mệnh đề GROUP BY để chọn các trạng thái duy nhất của khách hàng từ bảng customers.
SELECT
state
FROM
customers
GROUP BY state;
kết quả:
Bạn có thể đạt được kết quả tương tự bằng cách sử dụng mệnh đề DISTINCT:
Nói chung, mệnh đề DISTINCT là trường hợp đặc biệt của mệnh đề GROUP BY. Sự khác biệt giữa mệnh đề DISTINCT và mệnh đề GROUP BY là mệnh đề GROUP BY sắp xếp tập kết quả , trong khi mệnh đề DISTINCT thì không.
Lưu ý rằng MySQL 8.0 đã loại bỏ cách sắp xếp ngầm cho mệnh đề GROUP BY. Do đó, nếu bạn sử dụng MySQL 8.0+, bạn sẽ thấy tập kết quả của truy vấn trên với mệnh đề GROUP BY không được sắp xếp.
Nếu bạn thêm mệnh đề ORDER BY vào câu lệnh sử dụng mệnh đề DISTINCT đó, tập kết quả sẽ được sắp xếp và nó giống với kết quả được trả về bởi câu lệnh sử dụng mệnh đề GROUP BY.
SELECT DISTINCT
state
FROM
customers
ORDER BY
state;
GROUP BY để nhóm các hàng thành các nhóm con.Các bài viết liên quan:
Bình luận: