SQL Server中分组查询通常用于配合聚合函数，实现分类汇总统计的信息。而其分类汇总的本质实际上就是先将信息排序，排序后相同类别的信息会聚在一起，然后通过需求进行统计计算。 SQL Server中分组查询通常用于配合聚合函数，实现分类汇总统计的信息。而其分类汇总的本质实际上就是先将信息排序，排序后相同类别的信息会聚在一起，然后通过需求进行统计计算。 SQL Server中常用的数据分组相关查询如下： >[danger] ## GROUP BY- 根据指定列表达式列表中的值对查询结果进行分组。 ## SQL Server GROUP BY子句简介 `GROUP BY`子句用于按分组排列查询的行。 这些分组由在`GROUP BY`子句中指定的列确定。 以下是`GROUP BY`子句的语法： ~~~sql SELECT select_list FROM table_name GROUP BY column_name1, column_name2 ,...; ~~~ 在此查询语法中，`GROUP BY`子句为列中的每个值组合生成一个组。 请考虑以下示例： ~~~sql SELECT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) ORDER BY customer_id; ~~~ 执行上面查询语句，得到以下结果：  在此示例中，检索了客户ID为`1`，列是：`customer_id`和`order_date`。 从输出中可以清楚地看到，ID为`1`的客户在`2016`年下了一个订单，在`2018`年下了两个订单。ID为`2`的客户在`2017`年下了两个订单，在`2018`年下了一个订单。 在查询中添加一个`GROUP BY`子句来查看效果： ~~~sql SELECT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id; ~~~ 执行上面查询语句，得到以下结果：  `GROUP BY`子句将前三行分为两组，接下来的三行分为另外两组，具有客户ID和订单年份的唯一组合。 从功能上讲，上面查询中的`GROUP BY`子句产生的结果与使用`DISTINCT`子句的以下查询的结果相同： ~~~sql SELECT DISTINCT customer_id, YEAR (order_date) order_year FROM sales.orders WHERE customer_id IN (1, 2) ORDER BY customer_id; ~~~ 执行上面查询语句，得到以下结果：  #### 1\. GROUP BY子句和聚合函数 实际上，`GROUP BY`子句通常与聚合函数一起用于生成摘要报告。 聚合函数对组执行计算并返回每个组的唯一值。 例如，`COUNT()`函数返回每个组中的行数。 其他常用的聚合函数是：`SUM()`，`AVG()`，`MIN()`，`MAX()`。 `GROUP BY`子句将行排列成组，聚合函数返回每个组的摘要(总数量，最小值，最大值，平均值，总和等)。 例如，以下查询返回客户按年度下达的订单数： ~~~sql SELECT customer_id, YEAR (order_date) order_year, COUNT (order_id) 订单数量 FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id; ~~~ 执行上面查询语句，得到以下结果：  如果要引用`GROUP BY`子句中未列出的任何列或表达式，则必须使用该列作为聚合函数的输入。 否则，数据库系统将会提示错误，因为无法保证列或表达式将为每个组返回单个值。 例如，以下查询将失败： ~~~sql SELECT customer_id, YEAR (order_date) order_year, order_status FROM sales.orders WHERE customer_id IN (1, 2) GROUP BY customer_id, YEAR (order_date) ORDER BY customer_id; ~~~ > 这是因为`order_status`列未在`GROUP BY`子句中。 #### 2\. 更多GROUP BY子句示例 下面再举几个例子来理解`GROUP BY`子句的工作原理。 **2.1. 带有COUNT()函数示例的GROUP BY子句** 以下查询返回每个城市的客户数量： ~~~sql SELECT city, COUNT (customer_id) customer_count FROM sales.customers GROUP BY city ORDER BY city; ~~~ 执行上面查询语句，得到以下结果：  在此示例中，`GROUP BY`子句按城市将客户分组，`COUNT`函数返回每个城市的客户数。 同样，以下查询按州和城市返回客户数量。 ~~~sql SELECT city, state, COUNT (customer_id) customer_count FROM sales.customers GROUP BY state, city ORDER BY city, state; ~~~ 执行上面查询语句，得到以下结果：  **2.2. GROUP BY子句带有MIN和MAX函数示例** 以下声明返回所有型号年份为`2018`的最低和最高价产品： ~~~sql SELECT brand_name, MIN (list_price) min_price, MAX (list_price) max_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id WHERE model_year = 2018 GROUP BY brand_name ORDER BY brand_name; ~~~ 执行上面查询语句，得到以下结果：  在此示例中，`WHERE`子句在`GROUP BY`子句之前。 **2.3. 带有AVG()函数示例的GROUP BY子句** 以下语句使用`AVG()`函数返回型号年份为`2018`年的所有产品的平均价格： ~~~sql SELECT brand_name, AVG (list_price) avg_price FROM production.products p INNER JOIN production.brands b ON b.brand_id = p.brand_id WHERE model_year = 2018 GROUP BY brand_name ORDER BY brand_name; ~~~ 执行上面查询语句，得到以下结果：  **2.4. 带有SUM函数示例的GROUP BY子句** 请参阅以下`order_items`表：  以下查询使用`SUM()`函数获取每个订单的净值： ~~~sql SELECT order_id, SUM ( quantity * list_price * (1 - discount) ) net_value FROM sales.order_items GROUP BY order_id; ~~~ 执行上面查询语句，得到以下结果：  >[danger] ## HAVING - 指定组或聚合的搜索条件。 在本教程中，将学习如何使用SQL Server `HAVING`子句根据指定的条件筛选组。 `HAVING`子句通常与GROUP BY子句一起使用，以根据指定的条件列表过滤分组。 以下是`HAVING`子句的语法： ~~~sql SELECT select_list FROM table_name GROUP BY group_list HAVING conditions; ~~~ 在此语法中，`GROUP BY`子句将行汇总为分组，`HAVING`子句将一个或多个条件应用于这些每个分组。 只有使条件评估为`TRUE`的组才会包含在结果中。 换句话说，过滤掉条件评估为`FALSE`或`UNKNOWN`的组。 因为SQL Server在`GROUP BY`子句之后处理`HAVING`子句，所以不能通过使用列别名来引用选择列表中指定的聚合函数。以下查询将失败： ~~~sql SELECT column_name1, column_name2, aggregate_function (column_name3) column_alias FROM table_name GROUP BY column_name1, column_name2 HAVING column_alias > value; ~~~ 必须明确使用`HAVING`子句中的聚合函数表达式，如下所示： ~~~sql SELECT column_name1, column_name2, aggregate_function (column_name3) alias FROM table_name GROUP BY column_name1, column_name2 HAVING aggregate_function (column_name3) > value; ~~~ ## SQL Server HAVING示例 下面举一些例子来理解`HAVING`子句的工作原理。 #### 1\. HAVING子句与COUNT函数示例 请参阅示例数据库中的以下`orders`表：  以下声明查找每年至少下过两个订单的客户： ~~~sql SELECT customer_id, YEAR (order_date), COUNT (order_id) order_count FROM sales.orders GROUP BY customer_id, YEAR (order_date) HAVING COUNT (order_id) >= 2 ORDER BY customer_id; ~~~ 执行上面查询语句，得到以下结果：  在上面查询示例中， * 首先，`GROUP BY`子句按客户和订单年份对销售订单进行分组。 `COUNT()`函数返回每个客户每年下达的订单数。 * 其次，`HAVING`子句筛选出订单数至少为`2`的所有客户。 #### 2\. HAVING子句与SUM()函数的例子 请考虑以下`order_items`表：  以下语句查找净值大于`20000`的销售订单： ~~~sql SELECT order_id, SUM ( quantity * list_price * (1 - discount) ) net_value FROM sales.order_items GROUP BY order_id HAVING SUM ( quantity * list_price * (1 - discount) ) > 20000 ORDER BY net_value; ~~~ 执行上面查询语句，得到以下结果：  在这个例子中： * 首先，`SUM`函数计算销售订单的净值。 * 其次，`HAVING`子句过滤净值小于或等于`20000`的销售订单。 #### 3\. HAVING子句与MAX和MIN函数的示例 请参阅以下`products`表：  以下语句首先查找每个产品类别中的最大和最小价格。 然后，它筛选出最大价格大于`4000`或最小价格小于`500`的类别： ~~~sql SELECT category_id, MAX (list_price) max_list_price, MIN (list_price) min_list_price FROM production.products GROUP BY category_id HAVING MAX (list_price) > 4000 OR MIN (list_price) < 500; ~~~ 执行上面查询语句，得到以下结果：  #### 4\. HAVING子句与AVG()函数示例 以下语句查找平均价格介于`500`和`1000`之间的产品类别： ~~~sql SELECT category_id, AVG (list_price) avg_list_price FROM production.products GROUP BY category_id HAVING AVG (list_price) BETWEEN 500 AND 1000; ~~~ 执行上面查询语句，得到以下结果：  >[danger] ## GROUPING SETS - 生成多个分组集。 #### 设置销售摘要表 为了方便演示，下面创建一个名为`sales.sales_summary`的新表。 ~~~sql 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_summary FROM sales.order_items i INNER JOIN production.products p ON p.product_id = i.product_id INNER JOIN production.brands b ON b.brand_id = p.brand_id INNER JOIN production.categories c ON c.category_id = p.category_id GROUP BY b.brand_name, c.category_name, p.model_year ORDER BY b.brand_name, c.category_name, p.model_year; ~~~ 在此查询中，按品牌和类别检索销售额数据，并将其填充到`sales.sales_summary`表中。 以下查询从`sales.sales_summary`表返回数据：  #### SQL Server GROUPING SETS入门 根据定义，分组集是分组的一组列。 通常，具有聚合的单个查询定义单个分组集。 例如，以下查询定义了一个分组集，其中包括品牌和类别，表示为(品牌，类别)。 查询返回按品牌和类别分组的销售额： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY brand, category ORDER BY brand, category; ~~~ 执行上面查询语句，得到以下结果：  以下查询按品牌返回销售额。它定义了一个分组集(品牌)： ~~~sql SELECT brand, SUM (sales) sales FROM sales.sales_summary GROUP BY brand ORDER BY brand; ~~~ 执行上面查询语句，得到以下结果：  以下查询按类别返回销售额。 它定义了一个分组集(类别)： ~~~sql SELECT category, SUM (sales) sales FROM sales.sales_summary GROUP BY category ORDER BY category; ~~~ 执行上面查询语句，得到以下结果：  以下查询定义空分组集。 它返回所有品牌和类别的销售额。 ~~~sql SELECT SUM (sales) sales FROM sales.sales_summary; ~~~ 执行上面查询语句，得到以下结果：  上面的四个查询返回四个结果集，其中包含四个分组集： ~~~shell (brand, category) (brand) (category) () ~~~ 要使用所有分组集的聚合数据获得统一的结果集，可以使用`UNION ALL`运算符。 由于UNION ALL运算符要求所有结果集具有相同数量的列，因此需要将NULL添加到查询的选择列表中，如下所示： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY brand, category UNION ALL SELECT brand, NULL, SUM (sales) sales FROM sales.sales_summary GROUP BY brand UNION ALL SELECT NULL, category, SUM (sales) sales FROM sales.sales_summary GROUP BY category UNION ALL SELECT NULL, NULL, SUM (sales) FROM sales.sales_summary ORDER BY brand, category; ~~~ 执行上面查询语句，得到以下结果：  该查询生成了一个结果，其中包含了我们所期望的所有分组集的聚合。 但是，它有以下两个主要问题： * 查询非常冗长(看起来是不是很累？) * 查询很慢，因为SQL Server需要执行四个查询并将结果集合并为一个查询。 为了解决这些问题，SQL Server提供了一个名为`GROUPING SETS`的`GROUP BY`子句的子句。 `GROUPING SETS`在同一查询中定义多个分组集。 以下是`GROUPING SETS`的一般语法： ~~~sql SELECT column1, column2, aggregate_function (column3) FROM table_name GROUP BY GROUPING SETS ( (column1, column2), (column1), (column2), () ); ~~~ 此查询创建四个分组集： ~~~shell (column1,column2) (column1) (column2) () ~~~ 使用此`GROUPING SETS`重写获取销售数据的查询，如下所示： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY GROUPING SETS ( (brand, category), (brand), (category), () ) ORDER BY brand, category; ~~~ 执行上面查询语句，得到以下结果：  如上所示，查询产生的结果与使用`UNION ALL`运算符的结果相同。 但是，此查询更具可读性，当然也更有效。 **GROUPING函数** `GROUPING`函数指示是否聚合`GROUP BY`子句中的指定列。 它是聚合则返回`1`，或者为结果集是未聚合返回`0`。 请参阅以下查询示例： ~~~sql SELECT GROUPING(brand) grouping_brand, GROUPING(category) grouping_category, brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY GROUPING SETS ( (brand, category), (brand), (category), () ) ORDER BY brand, category; ~~~ 执行上面查询语句，得到以下结果：  `grouping_brand`列中的值表示该行是否已聚合，`1`表示销售额按品牌汇总，`0`表示销售金额未按品牌汇总。 相同的概念应用于`grouping_category`列。 >[danger] ## CUBE - 生成包含维列的所有组合的分组集。 ## SQL Server CUBE简介 分组集在单个查询中指定数据分组。 例如，以下查询定义表示为(品牌)的单个分组集： ~~~sql SELECT brand, SUM(sales) FROM sales.sales_summary GROUP BY brand; ~~~ 如果您没有学习过GROUPING SETS的使用，可使用以下查询创建`sales.sales_summary`表： ~~~sql 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_summary FROM sales.order_items i INNER JOIN production.products p ON p.product_id = i.product_id INNER JOIN production.brands b ON b.brand_id = p.brand_id INNER JOIN production.categories c ON c.category_id = p.category_id GROUP BY b.brand_name, c.category_name, p.model_year ORDER BY b.brand_name, c.category_name, p.model_year; ~~~ 即使以下查询不使用GROUP BY子句，它也会生成一个空的分组集，表示为`()`。 ~~~sql SELECT SUM(sales) FROM sales.sales_summary GROUP BY brand; ~~~ `CUBE`是`GROUP BY`子句的子句，用于生成多个分组集。 以下是`CUBE`的一般语法： ~~~sql SELECT d1, d2, d3, aggregate_function (c4) FROM table_name GROUP BY CUBE (d1, d2, d3); ~~~ 在此语法中，`CUBE`根据在`CUBE`子句中指定的维度列：`d1`，`d2`和`d3`生成所有可能的分组集。 上面的查询返回与以下查询相同的结果集，该查询使用`GROUPING SETS`： ~~~sql SELECT d1, d2, d3, aggregate_function (c4) FROM table_name GROUP BY GROUPING SETS ( (d1,d2,d3), (d1,d2), (d1,d3), (d2,d3), (d1), (d2), (d3), () ); ~~~ 如果在多维数据集中指定了`N`维列，则将具有`2N`个分组集。 通过部分使用`CUBE`可以减少分组集的数量，如以下查询所示： ~~~sql SELECT d1, d2, d3, aggregate_function (c4) FROM table_name GROUP BY d1, CUBE (d2, d3); ~~~ 在这种情况下，查询生成四个分组集，因为在`CUBE`中只指定了两个维列。 ## SQL Server CUBE示例 以下语句使用`CUBE`生成四个分组集： ~~~shell (brand, category) (brand) (category) () ~~~ 参考以下查询语句： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY CUBE(brand, category); ~~~ 执行上面查询语句，得到以下结果：  在此示例中，在`CUBE`子句中指定了两个维列，因此，我们总共有四个分组集。 以下示例说明如何执行部分`CUBE`以减少查询生成的分组集的数量： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY brand, CUBE(category); ~~~ 执行上面查询语句，得到以下结果：  >[danger] ## ROLLUP - 生成分组集，假设输入列之间存在层次结构。 ## SQL Server ROLLUP简介 SQL Server `ROLLUP`是`GROUP BY`子句的子句，它提供了定义多个分组集的简写。 与CUBE子句不同，`ROLLUP`不会根据维度列创建所有可能的分组集; `CUBE`是其中的一部分。 生成分组集时，`ROLLUP`假定维度列之间存在层次结构，并且仅基于此层次结构生成分组集。 `ROLLUP`通常用于生成小计和总计来生成报告目的。 考虑一个例子。 以下`CUBE(d1，d2，d3)`定义了八个可能的分组集： ~~~sql (d1, d2, d3) (d1, d2) (d2, d3) (d1, d3) (d1) (d2) (d3) () ~~~ 并且`ROLLUP(d1，d2，d3)`仅创建四个分组集，假设层次结构`d1> d2> d3`，如下所示： ~~~sql (d1, d2, d3) (d1, d2) (d1) () ~~~ `ROLLUP`通常用于计算分层数据的聚合，例如：按年>季度>月的销售额。 ## SQL Server ROLLUP语法 SQL Server `ROLLUP`的一般语法如下： ~~~sql SELECT d1, d2, d3, aggregate_function(c4) FROM table_name GROUP BY ROLLUP (d1, d2, d3); ~~~ 在此语法中，`d1`，`d2`和`d3`是维列。 该语句将根据层次结构`d1> d2> d3`计算列`c4`中的值的聚合。 还可以执行部分汇总以减少使用以下语法生成的小计： ~~~sql SELECT d1, d2, d3, aggregate_function(c4) FROM table_name GROUP BY d1, ROLLUP (d2, d3); ~~~ ## SQL Server ROLLUP示例 在这个示例中将重用在GROUPING SETS教程中创建的`sales.sales_summary`表进行演示。 如果尚未创建`sales.sales_summary`表，则可以使用以下语句创建它。 ~~~sql 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_summary FROM sales.order_items i INNER JOIN production.products p ON p.product_id = i.product_id INNER JOIN production.brands b ON b.brand_id = p.brand_id INNER JOIN production.categories c ON c.category_id = p.category_id GROUP BY b.brand_name, c.category_name, p.model_year ORDER BY b.brand_name, c.category_name, p.model_year; ~~~ 以下查询使用`ROLLUP`按品牌(小计)以及品牌和类别(总计)计算销售额。 ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY ROLLUP(brand, category); ~~~ 执行上面查询语句，得到以下结果：  在此示例中，查询假定品牌和类别之间存在层次结构，即：品牌>类别。 请注意，如果更改品牌和类别的顺序，结果将会有所不同，如以下查询所示： ~~~sql SELECT category, brand, SUM (sales) sales FROM sales.sales_summary GROUP BY ROLLUP (category, brand); ~~~ 在此示例中，层次结构是类别>品牌，执行上面查询语句，得到以下结果：  以下语句显示了如何执行部分汇总： ~~~sql SELECT brand, category, SUM (sales) sales FROM sales.sales_summary GROUP BY brand, ROLLUP (category); ~~~ 执行上面查询语句，得到以下结果：