CÂU LỆNH GROUP BY TRONG SQL

  -  

Trong khuyên bảo 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 để bố trí các bản ghi theo nhóm.

Bạn đang xem: Câu lệnh group by trong sql

Mệnh đề GROUP BY trong 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 bố trí các bạn dạng ghi của một tầm nã vấn theo nhóm. Các nhóm được xác định bởi các cột mà bạn chỉ định vào mệnh đề GROUP BY.

Sau đây 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 vấn vấn này, mệnh đề GROUP BY đã tạo thành một nhóm cho mỗi kết hợp những giá trị trong các 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 người sử dụng và năm mua hàng của những khách hà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 đang đặt một đơn hàng vào năm năm nhâm thìn và hai đơn hàng vào năm 2018. Quý khách có id hai sẽ đặt hai giao dịch vào năm 2017 và 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 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 đã bố trí ba bản ghi đầu tiên của công ty có id là 1 trong những thành nhì nhóm và ba bản ghi tiếp theo của công ty có id là 2 vào hai team khác với sự phối kết hợp duy nhất của id người tiêu dùng và năm đặt hàng.

Về phương diện chức năng, mệnh đề GROUP BY trong truy vấn vấn trên chế tác ra hiệu quả tương trường đoản cú như truy 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ới 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 để 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 nhóm và trả về một quý hiếm duy nhất cho mỗi nhóm. Ví dụ, hàm COUNT() trả về số lượng phiên bản ghi trong mỗi nhóm. Những hàm tập vừa lòng 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 bố trí các bản ghi thành những nhóm và tiến hành 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 sau đây trả về con số đơn để 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 bất kỳ 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 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 tồn tại gì bảo đảm an toàn rằng cột hoặc biểu thức này sẽ trả về một quý giá duy nhất cho từng nhóm.

Ví dụ: tầm nã vấn sau vẫn 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 vào SQL Server

Chúng ta hãy mang thêm một số trong những ví dụ nhằm hiểu cách hoạt động 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 tiếp sau đây trả về số lượng người sử dụ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 ví dụ như này, mệnh đề GROUP BY đội các người tiêu dùng lại với nhau theo từng tp và hàm COUNT() trả về số lượng người tiêu dùng ở từng thành phố.

Tương tự, tầm nã vấn dưới đây trả về số lượng quý khách theo tè bang cùng 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 Nguyenhostingvietnam.vn
*

GROUPING mix trong SQL Server

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

Theo định nghĩa GROUPING SETS là một 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 cùng một tập hợp khẳng định một tập hợp đội duy nhất.

Ví dụ: tầm nã vấn sau khẳng định một tập đúng theo nhóm bao gồm thương hiệu và hạng mục được bộc lộ là (brand, category). Tróc nã vấn trả về số tiền bán sản phẩm được team theo chữ tín và danh mục:

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

*
Truy vấn tiếp sau đây trả về số lượng bán sản phẩm theo yêu mến hiệu. Nó xác minh một tập hợp nhóm (brand):

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

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

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

*
Truy vấn sau khẳng định một tập hợp đội trống (). Nó trả về số tiền bán sản phẩm cho toàn bộ các chữ tín và danh mục.

SELECT SUM (sales) salesFROM sales.sales_summary;

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

(brand, category)(brand)(category)()Để đạt được tập hợp tác dụng thống độc nhất với dữ liệu tổng đúng theo cho toàn bộ các tập vừa lòng 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 cầu tất cả tập hợp tác dụng có thuộc số cột, bạn phải thêm cột NULL vào danh sách những 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 nhất với những tổng đúng theo cho tất cả các đội nhóm như cửa hàng chúng tôi mong đợi.

Xem thêm: 11 Bước Từ A Đến Z Khi Thiết Kế Website Như Thế Nào, Cách Để Thiết Kế Website (Kèm Ảnh)

Tuy nhiên, nó có hai vấn đề lớn:

Truy vấn hơi dài.Truy vấn chậm vì SQL vps cần thực hiện bốn tróc nã vấn con và phối kết hợp các tập hợp hiệu quả thành một.

Để tự khắc phục hầu hết sự cầm cố này, SQL Server cung ứng một mệnh đề con của mệnh đề GROUP BY được hotline là GROUPING SETS.

GROUPING SETS định nghĩa các nhóm trong và 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 nên bốn đội như sau:

(column1, column2)(column1)(column2)()Bạn hoàn toàn có thể sử dụng GROUPING SETS nhằm viết lại truy tìm vấn mang 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ũng có thể thấy, truy vấn vấn tạo ra ra công dụng giống như truy vấn thực hiện toán tử UNION ALL. Tuy nhiên, truy vấn vấn này dễ đọc hơn các 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 Nguyenhostingvietnam.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 nã vấn. Ví dụ: tầm nã vấn sau quan niệm một tập nhóm tốt nhất được ký kết hiệu là (brand):

SELECT brand, SUM(sales)FROM sales.sales_summaryGROUP BY brand;Nếu các 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 cách sử dụng tầm 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ả lúc truy vấn sau không thực hiện mệnh đề GROUP BY, nó tạo nên một tập hợp team trống được ký kết hiệu là ().

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

Truy vấn bên trên trả về cùng một tập hợp công dụng như truy 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 bạn có N cột được 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 nhóm 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 đúng theo này, tầm nã vấn tạo thành bốn tập đúng theo nhóm bởi vì chỉ gồm hai cột được chỉ định trong CUBE.

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

Câu lệnh sau áp 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à kết quả đầu ra:

*

Trung Nguyenhostingvietnam.vn

Mệnh đề ROLLUP vào SQL Server

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

ROLLUP trong SQL Server là một trong những mệnh đề con của mệnh đề GROUP BY cung ứng 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 team 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 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 phù hợp nhóm, ROLLUP mang định một hệ thống phân cung cấp giữa các cột sản phẩm công nghệ nguyên và chỉ còn tạo các tập hợp nhóm dựa trên hệ thống phân cấp này.

ROLLUP thường xuyên được thực hiện để tạo thành tổng số phụ (subtotals) và tổng số (totals) cho mục tiêu báo cáo.

Hãy để mắt tới ví dụ sau đây: mệnh đề CUBE (d1,d2,d3) sẽ xác định tám tập hợp đội 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 nên 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 thường được áp dụng để đo lường tổng vừa lòng của dữ liệu phân cấp cho như bán sản phẩm theo năm > quý > tháng.

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

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

Bạn cũng hoàn toàn có thể ROLLUP một trong những phần để sú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 trong SQL Server

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


Mệnh đề HAVING vào SQL Server

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

Mệnh đề HAVING thường xuyên đượ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 các đ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 đang tổng phù hợp các phiên bản ghi thành các 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ỉ những nhóm thỏa mãn nhu cầu điều khiếu nại trong mệnh đề HAVING bắt đầu được bao gồm trong tập kết quả.

Xem thêm: Chia Sẻ Iphone 4S Mất Hiển Thị Màn Hình, Iphone 4S Mất Hiển Thị

Vì SQL Server giải pháp xử lý mệnh đề HAVING sau mệnh đề GROUP BY, bạn không thể thực hiện bí danh cột nhằm tham chiếu đến những hàm tập vừa lòng được hướng dẫn và chỉ định 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 đó, bạn phải sử dụng các hàm tập thích 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 trong SQL Server

Hãy lấy một số trong những ví dụ nhằm hiểu cách HAVINGhoạt hễ của mệnh đề.

Sử dụng HAVING với hàm COUNT vào SQL Server

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

*

Câu lệnh dưới đây sử dụng mệnh đề HAVING nhằm tìm các quý 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 lấy một ví dụ này:

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