SQL Server ROLLUP

在上表中，我们可以看到我们有来自三个不同国家（美国、英国和印度）的员工。现在，基于这些表数据，**我们希望编写一个查询按国家/地区检索薪水**，如下所示

执行查询将返回以下输出

如果我们要根据国家/地区获取总薪水，此查询就很好。但当我们要的结果中也包含所有薪水的总计时，它不是一个更好的选择，因为它只能在一个级别上聚合数据，即小计，而不是总计。因此，我们将使用 ROLLUP 子句以及 GROUP BY 来检查类别的小计和总计，因为它扩展了 GROUP BY 子句的功能。

以下查询用于检索按国家/地区分组的薪水的小计和总计

我们将获得以下输出

我们也可以使用以下语法获得相同的输出

这是结果：

在输出中，我们可以看到每个国家/地区的总薪水，并且还会返回所有薪水的总计。ROLLUP 子句将在结果集中添加一个额外的行来显示总计。此处 NULL 代表总计。我们还可以使用 **COALESCE** 函数为其赋予有意义的名称，以将 NULL 值替换为提供的任何文本。

以下语句将用“Grand Total”替换 null 值

这是用给定文本替换 NULL 值的结果

多列的 ROLLUP

我们已经知道 ROLLUP 子句在层次结构的多个级别上执行聚合操作，并在单个查询中准备每个级别的汇总报告。

让我们通过一个例子来理解这个概念。假设 GROUP BY 子句中有多个列。在这种情况下，ROLLUP 子句假定 GROUP BY 子句中提供的列之间存在层次结构。每当列值发生变化时，查询会在结果末尾添加一个额外的超汇总摘要行。

例如，我们在 GROUP BY 子句中指定了三列，如下所示

ROLLUP 修饰符假定以下层次结构

并生成以下分组集

以下查询有助于更清楚地理解这一点

由于我们没有使用 ROLLUP 子句，基于 GROUP BY 子句中提供的多列对员工表进行的汇总将如下所示。在这种情况下，我们只会收到 **性别/国家/地区分析级别** 的汇总值。

现在我们将分析带有 ROLLUP 子句的输出

执行查询将显示以下输出，其中我们可以看到几行额外的行

此输出在三个不同的分析级别生成数据，如下所述

• 第一级为每组女性员工生成一个额外的子汇总摘要行，然后显示所有员工的总薪水。它会将 country 列设置为 NULL。
• 第二级为每组男性员工生成一个额外的子汇总摘要行，然后显示总薪水。它也会将 country 列设置为 NULL。
• 最后，第三级将生成一个额外的超汇总摘要行，显示所有列的总计。它会将 gender 和 country 列设置为 NULL。

它确保如果我们修改 GROUP BY 子句中定义的列的顺序，我们会得到不同的答案。请看下面的查询

这是结果：

与 ROLLUP 结合使用的 GROUPING 函数

GROUPING() 函数确定输出中的 NULL 是常规分组值、超汇总值还是总计。当 **NULL** 出现在超汇总行中时，它返回 1。否则，它返回 0。GROUPING() 函数可以在 select 列表、HAVING 子句和 ORDER BY 子句中使用。

请看下面的查询

执行查询将返回以下输出

在输出中，我们可以看到当 NULL 出现在gender 列的超汇总行中时，GROUPING(gender) 返回 1。否则，它将返回零。

类似地，当 NULL 出现在country 列的超汇总行中时，GROUPING(country) 返回 1。否则，它将返回零。

ROLLUP 与 CUBE 有何不同？

SQL Server 中的 ROLLUP 和 CUBE 用于报告目的，并显示小计和总计。我们通常将这些子句与 GROUP BY 子句一起使用。但是，它们用于相似的目标，但它们也存在一些差异。

ROLLUP 返回的输出显示所选列中层次结构值的聚合。另一方面，CUBE 创建一个结果集，其中包括所选列中所有可能的值组合的聚合。

让我们举一个例子来说明这些差异。假设我们有三列 c1、c2 和 c3。接下来，我们将进行不同级别的聚合。

如果我们将这些列与 **CUBE** 子句一起使用，例如 CUBE(c1, c2, c3)，我们将得到八种可能的分组集：

如果我们使用 **ROLLUP** 子句并将这些列作为 ROLLUP(c1, c2, c3)，我们将只得到四种可能的分组集：