Trong gợi ý này, các bạn sẽ tìm hiểu những mệnh đề GROUP BY, GROUPING SET, CUBE, ROLLUP với HAVING trong SQL vps để sắp xếp các phiên bản ghi theo nhóm.

Bạn đang xem: Sql server: các lệnh group by trong sql server có thể bạn chưa biết

Mệnh đề GROUP BY vào SQL Server

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

Mệnh đề GROUP BY trong SQL Server có thể chấp nhận được bạn sắp xếp các phiên bản ghi của một truy tìm vấn theo nhóm. Các nhóm được xác minh bởi những cột mà các bạn chỉ định vào mệnh đề GROUP BY.

Sau đây minh họa cú pháp mệnh đề GROUP BY vào 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 mỗi kết hợp các giá trị trong các cột được liệt kê trong mệnh đề GROUP BY.

Hãy xem lấy một ví dụ 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 một ví dụ này, chúng tôi đã truy tìm xuất tin tức id người sử dụng và năm đặt đơn hàng của những người sử dụng có id 1 cùng 2.

Như bạn có thể thấy ở công dụng đầu ra, khách hàng có id 1 sẽ đặt một giao dịch vào năm năm nhâm thìn và hai đơn hàng vào năm 2018. Người tiêu dùng có id hai sẽ đặt hai đơn hàng vào năm 2017 với một giao dịch vào năm 2018.

Hãy thêm mệnh đề GROUP BY vào tróc nã vấn trên và 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 đã thu xếp ba bản ghi đầu tiên của doanh nghiệp có id là một trong những thành nhì nhóm và ba bạn dạng ghi tiếp theo của công ty có id là 2 vào hai team khác cùng với sự phối hợp duy độc nhất của id khách hàng và năm đặt hàng.

Về phương diện chức năng, mệnh đề GROUP BY trong truy hỏi vấn trên chế tạo ra hiệu quả tương từ bỏ như truy vấn sau sử dụng 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 phù hợp của SQL Server

Trong thực tế, mệnh đề GROUP BY thường xuyên được sử dụng với những 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 thực hiện một phép tính vào một trong những nhóm với trả về một giá trị 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. Những hàm tập hòa hợp thường được áp dụng 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 sắp xếp các bản ghi thành những nhóm và thực hiện các hàm tập hợp (count, min, max, avg, sum, vv) cho mỗi nhóm.

Ví dụ: truy vấn vấn tiếp sau đây trả về con số đơn đặt hàng của bạn 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ê trong mệnh đề GROUP BY, chúng ta phải sử dụng cột kia làm đầu vào của hàm tập hợp.

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

Ví dụ: truy hỏi 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 mang thêm một trong những ví dụ nhằm hiểu cách buổi giao lưu của mệnh đề GROUP BY.

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

Truy vấn dưới đây trả về số lượng quý 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 ví dụ này, mệnh đề GROUP BY đội các 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 tìm vấn tiếp sau đây trả về số lượng quý khách theo tiểu bang và 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 Nguyenchuyenly.edu.vn
*

GROUPING mix trong SQL Server

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

Theo định nghĩa GROUPING SETS là 1 trong nhóm những cột mà chúng ta nhóm. Thông thường, một truy hỏi vấn với một tập hợp khẳng định một tập hợp đội duy nhất.

Ví dụ: truy nã vấn sau khẳng định một tập phù hợp nhóm bao gồm thương hiệu và hạng mục được bộc lộ là (brand, category). Truy tìm vấn trả về số tiền bán hàng được nhóm 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 sản phẩm theo mến hiệu. Nó xác định một tập hợp nhóm (brand):

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

*
Truy vấn dưới đây trả về số lượng bán hàng theo danh mục. Nó tư tưởng 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 minh một tập hợp đội trống (). Nó trả về số tiền bán hàng cho toàn bộ các chữ tín và danh mục.

SELECT SUM (sales) salesFROM sales.sales_summary;

*
Bốn tầm nã vấn bên trên trả về bốn tập kết quả với tứ nhóm:

(brand, category)(brand)(category)()Để có được tập hợp tác dụng thống nhất với dữ liệu tổng hợp cho toàn bộ các tập hợp nhóm, chúng ta cũng 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 tác dụng 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 nên một hiệu quả duy duy nhất với các tổng đúng theo cho toàn bộ các nhóm nhóm như shop chúng tôi mong đợi.

Xem thêm: Cách Dùng Nấm Linh Xanh : Công Dụng, Giá Bán Và Cách Dùng Hiệu Quả

Tuy nhiên, nó tất cả hai vụ việc lớn:

Truy vấn khá dài.Truy vấn chậm bởi SQL server cần thực hiện bốn truy vấn con và phối hợp các tập hợp kết quả thành một.

Để tự khắc phục số đông sự rứa này, SQL Server cung cấp 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 cùng một truy vấn. Sau đây là cú pháp tầm 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 team như sau:

(column1, column2)(column1)(column2)()Bạn có thể sử dụng GROUPING SETS để viết lại tróc nã vấn rước 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, tầm nã vấn tạo thành ra công dụng giống như truy nã vấn áp dụng toán tử UNION ALL. Mặc dù nhiên, truy hỏi vấn này dễ đọc hơn nhiều và tất nhiên là công dụng hơn.

Tìm đọc thêm về GROUPING mix trong SQL server ở bài viết sau:


Trung Nguyenchuyenly.edu.vn
*

Mệnh đề CUBE vào SQL Server

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

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

SELECT brand, SUM(sales)FROM sales.sales_summaryGROUP BY brand;Nếu bạn chưa tuân theo hướng dẫn GROUPING SETS, chúng ta cũng có thể tạo bảng sales.sales_summary bằng phương pháp sử dụng truy vấ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 p. 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 thực hiện mệnh đề GROUP BY, nó tạo thành một tập hợp nhóm trống được cam kết hiệu là ().

SELECT SUM(sales)FROM sales.sales_summary;CUBE là 1 mệnh đề nhỏ của mệnh đề GROUP BY được cho phép bạn tạo các nhóm. Tiếp sau đây minh họa cú pháp tầm 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 toàn bộ các nhóm rất có thể dựa trên các cột d1, d2 với d3 mà các bạn chỉ định trong mệnh đề CUBE.

Truy vấn trên trả về và một tập hợp công dụng như truy nã vấn sau, áp 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 dẫn và chỉ định 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 đội kết quả bằng phương pháp sử dụng CUBE một trong những 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 phù hợp này, truy vấn vấn tạo thành bốn tập đúng theo nhóm vì chưng chỉ gồm hai cột được chỉ định và hướng dẫn trong CUBE.

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

Câu lệnh sau sử dụng CUBE nhằm tạo tứ tập nhóm:

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

SELECT brand, category, SUM (sales) salesFROM sales.sales_summaryGROUP BY CUBE(brand, category);Đây là tác dụng đầu ra:

*

Trung Nguyenchuyenly.edu.vn

Mệnh đề ROLLUP trong SQL Server

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

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

Khi tạo các tập đúng theo nhóm, ROLLUP đưa định một hệ thống phân cấp cho giữa các cột sản phẩm công nghệ nguyên và chỉ tạo các tập hợp đội dựa trên khối hệ thống phân cung cấp này.

ROLLUP thường được áp dụng để tạo ra tổng số phụ (subtotals) cùng tổng số (totals) cho mục đích báo cáo.

Hãy để mắt tới ví dụ sau đây: mệnh đề CUBE (d1,d2,d3) sẽ khẳng định tám tập hợp team 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 để tính toán tổng thích hợp của tài liệu phân cấp như bán hàng theo năm > quý > tháng.

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

Cú pháp bình thường của ROLLUP trong SQL vps 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à d3 là những cột máy nguyên. Câu lệnh công thêm tổng các giá trị trong cột c4 dựa vào thứ bậc d1> d2> d3.

Bạn cũng rất có thể ROLLUP một phần để sút tổng số phụ (subtotals) được tạo bằng cách 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ẽ sử dụng lại bảng sales.sales_summary vào cơ sở dữ liệu mẫu BikeStores đã tạo ra trong phần khuyên bảo về GROUPING SETS nhằm minh họa. Nếu bạn chưa chế tạo bảng sales.sales_summary, chúng ta có thể sử dụng câu lệnh sau để tạo thành 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 p 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 sau đây sử dụng ROLLUP để đo lường và tính toán số tiền bán hàng theo uy tín (tổng số phụ - subtotals) cùng cả chữ tín 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 lấy ví dụ như này, truy vấn mang định rằng có một hệ thống phân cung cấp giữa uy tín và danh mục, sẽ 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 sử 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 sẽ tổng thích hợp các bản ghi thành các nhóm cùng mệnh đề HAVING sẽ vận dụng một hoặc các điều kiện cho các nhóm này.

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

Vì SQL Server xử trí mệnh đề HAVING sau mệnh đề GROUP BY, các bạn không thể áp dụng bí danh cột để tham chiếu đến những hàm tập thích hợp được hướng đẫn trong câu lệnh SELECT. Truy vấn dưới đâ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 đó, chúng ta phải sử dụng các hàm tập hợp trực tiếp trong mệnh đề HAVING một cách ví dụ 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ố trong những 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 vào SQL Server

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

*

Câu lệnh tiếp sau đây sử dụng mệnh đề HAVING để tìm các khách hàng đã đặt ít nhất hai đơn hàng 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 ví dụ như này:

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