Mệnh đề group by trong sql server

     

Trong trả lời này, các bạn sẽ tìm hiểu những mệnh đề GROUP BY, GROUPING SET, CUBE, ROLLUP cùng HAVING trong SQL server để sắp xếp các bạn dạng ghi theo nhóm.

Bạn đang xem: Mệnh đề group by trong sql server

Mệnh đề GROUP BY trong SQL Server

Giới thiệu về mệnh đề GROUP BY vào SQL Server

Mệnh đề GROUP BY vào SQL Server cho phép bạn sắp xếp các bản ghi của một tróc nã vấn theo nhóm. Các nhóm được khẳng định bởi các cột mà chúng ta chỉ định vào mệnh đề GROUP BY.

Sau phía trên minh họa cú pháp mệnh đề GROUP BY trong SQL Server:

SELECT select_listFROM table_nameGROUP BY column_name1, column_name2 ,...;Trong truy tìm vấn này, mệnh đề GROUP BY đã tạo ra một nhóm cho từng kết hợp những giá trị trong những cột được liệt kê vào mệnh đề GROUP BY.

Hãy xem ví dụ như sau:

SELECT customer_id, YEAR (order_date) order_yearFROM sales.ordersWHERE customer_id IN (1, 2)ORDER BY customer_id;Đây là kết quả:

*

Trong lấy ví dụ như này, chúng tôi đã truy xuất tin tức id quý khách hàng và năm đặt đơn hàng của những quý khách có id 1 với 2.

Như chúng ta cũng có thể thấy ở tác dụng đầu ra, người sử dụng có id 1 đã đặt một giao dịch vào năm 2016 và hai giao dịch vào năm 2018. Người tiêu dùng có id hai đã đặt hai deals vào năm 2017 cùng một đơn hàng vào năm 2018.

Hãy thêm mệnh đề GROUP BY vào truy nã vấn trên cùng xem kết quả:

SELECT customer_id, YEAR (order_date) order_yearFROM sales.ordersWHERE customer_id IN (1, 2)GROUP BY customer_id, YEAR (order_date)ORDER BY customer_id;Đây là kết quả:

*

Mệnh đề GROUP BY đã sắp xếp ba bản ghi đầu tiên của chúng ta có id là 1 thành nhì nhóm cùng ba bạn dạng ghi tiếp theo của doanh nghiệp có id là 2 vào hai đội khác với sự phối kết hợp duy nhất của id người sử dụng và năm đặt hàng.

Về mặt chức năng, mệnh đề GROUP BY trong truy vấn vấn trên tạo thành ra tác dụng tương tự như truy hỏi vấn sau thực hiện mệnh đề DISTINCT:

SELECT DISTINCT customer_id, YEAR (order_date) order_yearFROM sales.ordersWHERE customer_id IN (1, 2)ORDER BY customer_id;Đây là kết quả:

*

Mệnh đề GROUP BY và hàm tập hòa hợp của SQL Server

Trong thực tế, mệnh đề GROUP BY hay được áp dụng với các hàm tập hợp nhằm tạo báo cáo tóm tắt.

Một hàm tập hợp triển khai một phép tính vào trong 1 nhóm và trả về một quý giá duy nhất cho từng nhóm. Ví dụ, hàm COUNT() trả về số lượng phiên bản ghi trong những nhóm. Các hàm tập thích hợp thường được thực hiện khác là SUM() (tính tổng), AVG() (tính trung bình), MIN()(nhỏ nhất), MAX()(lớn nhất).

Mệnh đề GROUP BY thu xếp các phiên bản ghi thành các nhóm và triển khai các hàm tập hòa hợp (count, min, max, avg, sum, vv) cho mỗi nhóm.

Ví dụ: tróc nã vấn sau đây trả về số lượng đơn đặt hàng của khách hàng theo năm:

SELECT customer_id, YEAR (order_date) order_year, COUNT (order_id) order_placedFROM sales.ordersWHERE customer_id IN (1, 2)GROUP BY customer_id, YEAR (order_date)ORDER BY customer_id; Đây là kết quả:

*

Nếu bạn có nhu cầu thêm ngẫu nhiên cột hoặc biểu thức nào ko được liệt kê vào mệnh đề GROUP BY, chúng ta phải áp dụng cột kia làm nguồn vào của hàm tập hợp.

Nếu không, bạn sẽ gặp lỗi vì không có gì bảo đảm an toàn rằng cột hoặc biểu thức này sẽ trả về một giá trị duy nhất cho mỗi nhóm.

Ví dụ: truy nã vấn sau đã thất bại:

SELECT customer_id, YEAR (order_date) order_year, order_statusFROM sales.ordersWHERE customer_id IN (1, 2)GROUP BY customer_id, YEAR (order_date)ORDER BY customer_id;

Ví dụ về mệnh đề GROUP BY trong SQL Server

Chúng ta hãy rước thêm một số ví dụ để hiểu cách hoạt động vui chơi của mệnh đề GROUP BY.

Sử dụng mệnh đề GROUP BY cùng với hàm COUNT() trong SQL Server

Truy vấn tiếp sau đây trả về số lượng khách hàng ở từng thành phố:

SELECT city, COUNT (customer_id) customer_countFROM sales.customersGROUP BY cityORDER BY city;Đây là kết quả:

*

Trong lấy một ví dụ này, mệnh đề GROUP BY team các quý khách hàng lại cùng nhau theo từng tp và hàm COUNT() trả về số lượng quý khách hàng ở từng thành phố.

Tương tự, truy vấn vấn sau đây trả về số lượng người tiêu dùng theo tiểu bang với thành phố.

SELECT city, state, COUNT (customer_id) customer_countFROM sales.customersGROUP BY state, cityORDER BY city, state;Đây là kết quả:

*

Trung Nguyensofaxuong.vn
*

GROUPING phối trong SQL Server

Giới thiệu về GROUPING SETS vào SQL Server

Theo quan niệm GROUPING SETS là 1 trong nhóm những cột mà bạn nhóm. Thông thường, một tầm nã vấn với một tập hợp xác minh một tập hợp nhóm duy nhất.

Ví dụ: truy nã vấn sau xác minh một tập thích hợp nhóm bao gồm thương hiệu và danh mục được bộc lộ là (brand, category). Truy tìm vấn trả về số tiền bán hàng được team theo uy tín và danh mục:

SELECT brand, category, SUM (sales) salesFROM sales.sales_summaryGROUP BY brand, categoryORDER BY brand, category;

*
Truy vấn dưới đây trả về số lượng bán hàng theo yêu mến hiệu. Nó khẳng định một tập hợp đội (brand):

SELECT brand, SUM (sales) salesFROM sales.sales_summaryGROUP BY brandORDER BY brand;

*
Truy vấn tiếp sau đây trả về số lượng bán sản phẩm theo danh mục. Nó có mang một tập hợp đội (category):

SELECT category, SUM (sales) salesFROM sales.sales_summaryGROUP BY categoryORDER BY category;

*
Truy vấn sau xác định một tập hợp đội trống (). Nó trả về số tiền bán hàng cho tất cả các thương hiệu và danh mục.

SELECT SUM (sales) salesFROM sales.sales_summary;

*
Bốn truy vấn vấn bên trên trả về tứ tập công dụng với tư nhóm:

(brand, category)(brand)(category)()Để giành được tập hợp tác dụng thống tuyệt nhất với tài liệu tổng hòa hợp cho tất cả các tập đúng theo nhóm, chúng ta có thể sử dụng toán tử UNION ALL.

Vì toán tử UNION ALL yêu thương cầu toàn bộ tập hợp hiệu quả có cùng số cột, bạn cần thêm cột NULL vào danh sách các truy vấn như sau:

SELECT brand, category, SUM (sales) salesFROM sales.sales_summaryGROUP BY brand, categoryUNION ALLSELECT brand, NULL, SUM (sales) salesFROM sales.sales_summaryGROUP BY brandUNION ALLSELECT NULL, category, SUM (sales) salesFROM sales.sales_summaryGROUP BY categoryUNION ALLSELECT NULL, NULL, SUM (sales)FROM sales.sales_summaryORDER BY brand, category;

*
Truy vấn tạo ra một tác dụng duy tốt nhất với những tổng hòa hợp cho tất cả các đội nhóm như cửa hàng chúng tôi mong đợi.

Xem thêm: Soạn Ngữ Văn 8 Ngắm Trăng (Vọng Nguyệt), Soạn Bài Ngắm Trăng (Vọng Nguyệt)

Tuy nhiên, nó có hai sự việc lớn:

Truy vấn hơi dài.Truy vấn chậm bởi vì SQL server cần triển khai bốn truy nã vấn bé và kết hợp các tập hợp kết quả thành một.

Để xung khắc phục gần như sự thế này, SQL Server hỗ trợ một mệnh đề con của mệnh đề GROUP BY được call là GROUPING SETS.

GROUPING SETS định nghĩa nhiều nhóm trong và một truy vấn. Sau đây là cú pháp thông thường của GROUPING SETS:

SELECT column1, column2, aggregate_function (column3)FROM table_nameGROUP BY GROUPING SETS ( (column1, column2), (column1), (column2), ());Truy vấn này tạo thành bốn nhóm như sau:

(column1, column2)(column1)(column2)()Bạn rất có thể sử dụng GROUPING SETS để viết lại tầm nã vấn đem dữ liệu bán hàng như sau:

SELECTbrand,category,SUM (sales) salesFROMsales.sales_summaryGROUP BYGROUPING SETS ((brand, category),(brand),(category),())ORDER BYbrand,category;Như bạn có thể thấy, truy vấn vấn chế tác ra công dụng giống như truy tìm vấn thực hiện toán tử UNION ALL. Tuy nhiên, truy vấn này đọc dễ dàng hơn nhiều và tất yếu là tác dụng hơn.

Tìm gọi thêm về GROUPING set trong SQL server ở nội dung bài viết sau:


Trung Nguyensofaxuong.vn
*

Mệnh đề CUBE vào SQL Server

Giới thiệu về mệnh đề CUBE vào SQL Server

GROUPING SETS định nghĩa các nhóm tài liệu trong một truy vấn vấn. Ví dụ: truy nã vấn sau khái niệm một tập nhóm độc nhất được ký kết hiệu là (brand):

SELECT brand, SUM(sales)FROM sales.sales_summaryGROUP BY brand;Nếu bạn chưa làm theo hướng dẫn GROUPING SETS, bạn có thể tạo bảng sales.sales_summary bằng cách sử dụng truy nã vấn sau:

SELECT b.brand_name AS brand, c.category_name AS category, p.model_year, round( SUM ( quantity * i.list_price * (1 - discount) ), 0 ) sales INTO sales.sales_summaryFROM sales.order_items iINNER JOIN production.products phường ON p.product_id = i.product_idINNER JOIN production.brands b ON b.brand_id = p.brand_idINNER JOIN production.categories c ON c.category_id = p.category_idGROUP BY b.brand_name, c.category_name, p.model_yearORDER BY b.brand_name, c.category_name, p.model_year;Ngay cả khi truy vấn sau không sử dụng mệnh đề GROUP BY, nó tạo thành một tập hợp nhóm trống được ký hiệu là ().

SELECT SUM(sales)FROM sales.sales_summary;CUBE là một trong mệnh đề con của mệnh đề GROUP BY được cho phép bạn tạo những nhóm. Dưới đây minh họa cú pháp bình thường của CUBE:

SELECT d1, d2, d3, aggregate_function (c4)FROM table_nameGROUP BY CUBE (d1, d2, d3);Trong cú pháp này, hàm CUBE tạo tất cả các nhóm có thể dựa trên các cột d1, d2 với d3 mà bạn chỉ định vào mệnh đề CUBE.

Truy vấn trên trả về cùng một tập hợp kết quả như truy tìm vấn sau, sử dụng hàm GROUPING SETS:

SELECT d1, d2, d3, aggregate_function (c4)FROM table_nameGROUP BY GROUPING SETS ( (d1,d2,d3), (d1,d2), (d1,d3), (d2,d3), (d1), (d2), (d3), () );Nếu chúng ta có N cột được hướng đẫn trong CUBE, các bạn sẽ có 2 N tập đội kết quả.

Có thể giảm số lượng tập nhóm kết quả bằng cách sử dụng CUBE 1 phần như được hiển thị trong truy nã vấn sau:

SELECT d1, d2, d3, aggregate_function (c4)FROM table_nameGROUP BY d1, CUBE (d2, d3);Trong trường thích hợp này, truy tìm vấn tạo nên bốn tập đúng theo nhóm vì chưng chỉ có hai cột được hướng dẫn và chỉ định trong CUBE.

Ví dụ về mệnh đề CUBE trong SQL Server

Câu lệnh sau áp dụng CUBE nhằm tạo bốn tập nhóm:

(brand, category)(brand)(category)()

SELECT brand, category, SUM (sales) salesFROM sales.sales_summaryGROUP BY CUBE(brand, category);Đây là kết quả đầu ra:

*

Trung Nguyensofaxuong.vn

Mệnh đề ROLLUP trong SQL Server

Giới thiệu về mệnh đề ROLLUP trong SQL Server

ROLLUP trong SQL Server là một mệnh đề nhỏ của mệnh đề GROUP BY hỗ trợ cách viết tắt để xác định nhiều GROUPING SETS. Không giống như CUBE - tạo các tập đội với toàn bộ các phối hợp của các cột, ROLLUP không tạo toàn bộ các tập hợp nhóm rất có thể dựa trên những cột sản phẩm nguyên.

Khi tạo các tập hợp nhóm, ROLLUP mang định một hệ thống phân cấp cho giữa các cột đồ vật nguyên và chỉ còn tạo những tập hợp team dựa trên hệ thống phân cung cấp này.

ROLLUP hay được áp dụng để tạo thành tổng số phụ (subtotals) với tổng số (totals) cho mục tiêu báo cáo.

Hãy chú ý ví dụ sau đây: mệnh đề CUBE (d1,d2,d3) sẽ xác định tám tập hợp nhóm như sau:

(d1, d2, d3)(d1, d2)(d2, d3)(d1, d3)(d1)(d2)(d3)()Nhưng mệnh đề ROLLUP(d1,d2,d3) chỉ tạo thành bốn tập hợp team (giả sử phân cung cấp d1 > d2 > d3) như sau:

(d1, d2, d3)(d1, d2)(d1)()Mệnh đề ROLLUP hay được sử dụng để đo lường và thống kê tổng hợp của tài liệu phân cung cấp như bán sản phẩm theo năm > quý > tháng.

Cú pháp của mệnh đề ROLLUP trong SQL Server

Cú pháp tầm thường của ROLLUP vào SQL server như sau:

SELECT d1, d2, d3, aggregate_function(c4)FROM table_nameGROUP BY ROLLUP (d1, d2, d3);Trong cú pháp này, d1, d2 với d3 là những cột vật dụng nguyên. Câu lệnh và tính tổng các giá trị trong cột c4 dựa trên thứ bậc d1> d2> d3.

Bạn cũng có thể ROLLUP một trong những phần để bớt tổng số phụ (subtotals) được tạo bằng phương pháp sử dụng cú pháp sau:

SELECT d1, d2, d3, aggregate_function(c4)FROM table_nameGROUP BY d1, ROLLUP (d2, d3);

Ví dụ về mệnh đề ROLLUP vào SQL Server

Chúng ta sẽ thực hiện lại bảng sales.sales_summary vào cơ sở dữ liệu mẫu BikeStores đã chế tạo trong phần khuyên bảo về GROUPING SETS nhằm minh họa. Nếu như khách hàng chưa tạo thành bảng sales.sales_summary, bạn cũng có thể sử dụng câu lệnh sau để tạo bảng.

SELECT b.brand_name AS brand, c.category_name AS category, p.model_year, round( SUM ( quantity * i.list_price * (1 - discount) ), 0 ) sales INTO sales.sales_summaryFROM sales.order_items iINNER JOIN production.products phường ON p.product_id = i.product_idINNER JOIN production.brands b ON b.brand_id = p.brand_idINNER JOIN production.categories c ON c.category_id = p.category_idGROUP BY b.brand_name, c.category_name, p.model_yearORDER BY b.brand_name, c.category_name, p.model_year;Truy vấn dưới đây sử dụng ROLLUP để giám sát và đo lường số tiền bán sản phẩm theo chữ tín (tổng số phụ - subtotals) cùng cả thương hiệu và hạng mục (tổng số - totals).

SELECT brand, category, SUM (sales) salesFROM sales.sales_summaryGROUP BY ROLLUP(brand, category);Đây là đầu ra:

*

Trong ví dụ này, truy nã vấn đưa định rằng có một khối hệ thống phân cấp giữa thương hiệu và danh mục, chính là thương hiệu > danh mục.


Mệnh đề HAVING trong SQL Server

Giới thiệu về mệnh đề HAVING trong SQL Server

Mệnh đề HAVING thường được áp dụng với mệnh đề GROUP BY nhằm lọc những nhóm dựa trên một danh sách những điều kiện. Tiếp sau đây minh họa cú pháp mệnh đề HAVING:

SELECT select_listFROM table_nameGROUP BY group_listHAVING conditions;Trong cú pháp này, mệnh đề GROUP BY vẫn tổng phù hợp các phiên bản ghi thành những nhóm và mệnh đề HAVING sẽ vận dụng một hoặc những điều kiện cho các nhóm này.

Chỉ các nhóm thỏa mãn nhu cầu điều khiếu nại trong mệnh đề HAVING new được bao gồm trong tập kết quả.

Xem thêm: Giáo Án Sử 10 Bài 6 : Các Quốc Gia Ấn Và Văn Hóa Truyền Thống Ấn Độ

Vì SQL Server cách xử trí mệnh đề HAVING sau mệnh đề GROUP BY, các bạn không thể áp dụng bí danh cột nhằm tham chiếu đến những hàm tập vừa lòng được chỉ định trong câu lệnh SELECT. Truy nã vấn tiếp sau đây sẽ thất bại:

SELECT column_name1, column_name2, aggregate_function (column_name3) column_aliasFROM table_nameGROUP BY column_name1, column_name2HAVING column_alias > value;Thay vào đó, các bạn phải sử dụng các hàm tập phù hợp trực tiếp trong mệnh đề HAVING một cách rõ ràng như sau:

SELECT column_name1, column_name2, aggregate_function (column_name3) aliasFROM table_nameGROUP BY column_name1, column_name2HAVING aggregate_function (column_name3) > value;

Ví dụ về mệnh đề HAVING vào SQL Server

Hãy lấy một số ví dụ để hiểu biện pháp HAVINGhoạt rượu cồn của mệnh đề.

Sử dụng HAVING cùng với hàm COUNT trong SQL Server

Chúng ta sẽ sử dụng bảng orders trong cơ sở tài liệu mẫu BikeStores để minh họa:

*

Câu lệnh tiếp sau đây sử dụng mệnh đề HAVING nhằm tìm các quý khách hàng đã đặt tối thiểu hai deals mỗi năm:

SELECT customer_id, YEAR (order_date), COUNT (order_id) order_countFROM sales.ordersGROUP BY customer_id, YEAR (order_date)HAVING COUNT (order_id) >= 2ORDER BY customer_id;

*
Trong lấy ví dụ này:

Đầu tiên, mệnh đề GROUP BY nhóm các đơn đặt hàng theo quý khách và năm để hàng. Hàm COUNT() trả về số lượng đơn mua hàng mỗi quý khách hàng được đặt trong mỗi năm.Thứ hai, mệnh đề HAVING sẽ lọc bỏ tất cả các quý khách hàng có số lượng đơn đặt hàng ít rộng hai.