在这篇文章中，我们将学习如何在SQL查询中计算和添加小计。
简介
小计是一个显示类似数据集之和的数字，但它并不表示最终的总数。 小计主要用于销售、财务和会计报告。同时，我们可以在收据中注意到这个数字，它们通常位于税前。
在SQL查询中计算小计可能比普通的汇总查询要复杂一些。然而，SQL Server提供了一些GROUP BY扩展，帮助我们解决这个问题。在本文的下一节，我们将发现这些扩展，它们有助于在SQL查询中计算小计。
预先要求
在下面的文章例子中，我们将使用SalesList样板表，下面的查询有助于创建这个样板表，它还将向这个表填充一些样板数据。
CREATE TABLE 销售清单 (SalesMonth NVARCHAR（20）, SalesQuartesVARCHAR（5）, SalesYearSMALLINT, SalesTotal MONEY) 去 INSERT INTOSalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'March','Q1',2019,60) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'March','Q1',2020,50) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'May','Q2',2019,30) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'July','Q3',2020,10) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'11月','Q4',2019,120) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'10月','Q4',2019,150) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'11月','Q4',2019,180) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'11月','Q4',2020,120) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'July','Q3',2019,160) INSERT INTO SalesList(SalesMonth,SalesQuartes,SalesYear,SalesTotal) VALUES (N'March','Q1',2020,170) 去 SELECT* FROM SalesList |
了解ROLLUP扩展
GROUP BY语句用于将具有相同值的记录分组到一个新的汇总行中，它是汇总查询的主角。ROLLUP, CUBE, 和GROUPING SETS是GROUP BY语句的扩展，并在结果集中添加额外的小计和大计行。为了在SQL查询中计算小计，我们可以使用ROLLUP扩展的GROUP BY语句。ROLLUP扩展允许我们根据其输入列生成分层的小计行，并且它还会在结果集中增加一个大计行。例如，**GROUP BY ROLLUP (SalesYear)**语句只在结果集中添加所有年份的总计，但是GROUP BY ROLLUP (SalesYear, SalesQuartes)语句会在结果集中添加以下额外的行。
SalesYear,NULL -> Subtotal
NULL ,NULL -> Grand total
作为最后一点，与ROLLUP扩展有关，小计的组合将取决于所传递的列参数。为了更好地理解这个概念，我们将看一下下面的例子。
例子-1:
SELECTSalesYear, SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY ROLLUP(SalesYear) |
我们可以看到在结果集的末尾有一个额外的行，这个行显示了各年的总销售额，而没有考虑销售月份和季度。
例2:
在这个例子中，我们将传递两个不同的列作为ROLLUP的参数。在这种情况下，ROLLUP将额外的小计和总计行添加到结果集中。
SELECTSalesYear,SalesQuartes, SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY ROLLUP(SalesYear, SalesQuartes) |
在查询的结果中，标有数字1和2的行表示各年的小计。 标有数字3的行表示各年的全部销售总和。
例3:
在这个例子中，我们将向ROLLUP扩展传递3个列，然后这个扩展将生成所有层次的小计行。
SELECTSalesYear,SalesQuartes,SalesMonth ,SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY ROLLUP(SalesYear, SalesQuartes, SalesMonth) |
了解GROUPING函数
GROUPING函数用于确定GROUP BY列表中的列是否已经被聚集。因此，我们可以用这个函数来识别NULL值并替换它们。
SELECT SalesYear, SalesQuartes, SUM(SalesTotal) AS SalesTotal , GROUPING(SalesQuartes) AS SalesQuarterGrp, GROUPING(SalesYear) AS SYearGrp FROM SalesList GROUP BY ROLLUP(SalesYear, SalesQuartes) |
我们可以看到，分组的行是由GROUPING函数决定的。现在我们将一起使用SQL CASE语句和GROUPING函数，这样我们将用更有意义的解释来替换NULL值。
SELECT CASE WHEN GROUPING(SalesQuartes)=1 AND GROUPING(SalesYear)=0 THEN 'SubTotal' （小计 WHEN GROUPING(SalesQuartes)=1 AND GROUPING(SalesYear)=1 那么 '总计'。 否则 CAST(SalesYear AS varchar(10)) 结束 AS SalesYear, 销售量。 SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY ROLLUP(SalesYear,SalesQuartes) |
结果，在ROLLUP扩展的帮助下，我们在结果集中添加了小计和大计行。
在SQL查询中只为一列计算小计
在某些情况下，我们可能需要在SQL查询中只为一列添加小计。在这种情况下，我们可以同时使用ROW_NUMBER()和NEWID()函数来处理这个问题。这种组合使用的原因是为每一行添加一个唯一的数字，然后我们将使用这个编号的行进行分组。
SELECT SalesMonth,SalesTotal , ROW_NUMBER() OVER(ORDER BY NEWID()) AS RowNumber FROM SalesList |
作为第二步，我们需要对这个结果集进行临时处理，以汇总销售金额并添加额外的小计行。CTE（Common Table Expressions）是最佳选择，因为它允许我们定义临时命名的结果集。
with cte as ( SELECT SalesMonth,SalesTotal , row_number() over(order by newid()) AS RowNumber FROM SalesList ) 选择 RowNumber ,SalesMonth,SUM(SalesTotal) AS SalesTotal FROM CTE GROUP BY ROLLUP(SalesMonth, RowNumber) |
在这个结果集中，我们需要删除RowNumber列，只显示SalesMonth列和它的小计行。
with cte as ( SELECT SalesMonth,SalesTotal , row_number() over(order by newid()) AS RowNumber FROM SalesList ) 选择 CASE WHEN GROUPING(RowNumber) =1 THEN "小计 ELSE 销售月 END AS SalesMonth,SUM(SalesTotal) AS SalesTotal FROM CTE GROUP BY ROLLUP(SalesMonth, RowNumber) |
在这个数据集中，显示为黄色的行不是一个小计行，所以我们需要避免这个行出现在我们查询的结果输出中。为了做到这一点，我们将再次使用GROUPING函数并过滤这一行。
with cte as ( SELECT SalesMonth,SalesTotal , row_number() over(order by newid()) AS RowNumber FROM SalesList ) 选择 CASE WHEN GROUPING(RowNumber) =1 THEN "小计 ELSE 销售月 END AS SalesMonth,SUM(SalesTotal) AS SalesTotal FROM CTE GROUP BY ROLLUP(SalesMonth, RowNumber) HAVING GROUPING(SalesMonth) = 0 |
从结果集可以看出，我们在SQL查询中借助ROLLUP扩展添加了一个小计。
使用GROUPING SET扩展作为一种替代方法
GROUPING SETS是另一个GROUP BY扩展，它允许我们在一个查询中显示多个分组集。例如，当我们想在一个结果集中显示月度和季度的销售额时，我们可以使用UNION ALL语句，但这将是一个不实际的方法。
SELECT NULL AS SalesQuarter, SalesMonth, SUM(SalesTotal) AS SalesTotal FROM 销售清单 GROUP BY SalesMonth UNION ALL SELECTSalesQuartes, NULL AS SalesMonth, SUM(SalesTotal) AS SalesTotal 从 销售清单 GROUP BY SalesQuartes |
GROUPING SETS扩展可以只用一个查询来创建相同的结果集。
SELECT SalesQuartes,SalesMonth , SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY GROUPING SETS(SalesQuartes,SalesMonth) |
同时，我们可以在SQL查询中使用GROUPING SETS来增加小计。通过下面的查询，我们可以创建小计。
选择 CASE WHEN GROUPING(SalesQuartes)=1 AND GROUPING(SalesYear)=0 THEN '小计' WHEN GROUPING(SalesQuartes)=1 AND GROUPING(SalesYear)=1 那么 '总计'。 否则 CAST(SalesYear AS varchar(10)) 结束 AS SalesYear, 销售量。 SUM(SalesTotal) AS SalesTotal FROM SalesList GROUP BY GROUPING SETS(SalesYear,(SalesYear,SalesQuartes),()) |
总结
在这篇文章中，我们学习了ROLLUP和GROUPING SETS扩展在SQL查询中计算小计的用法。
Esat Erkec是一名SQL Server专业人员，8年多前开始了他的职业生涯，成为一名软件开发人员。他是一名SQL Server微软认证解决方案专家。
他职业生涯的大部分时间都集中在SQL Server数据库管理和开发方面。他目前的兴趣是数据库管理和商业智能。你可以在LinkedIn上找到他。
