SQL CUBE

摘要：在本教程中，你将学习如何使用 SQL CUBE 为查询输出生成小计。

SQL CUBE 简介

与 ROLLUP 类似，CUBE 是 GROUP BY 子句的扩展。CUBE 允许你像 ROLLUP 扩展那样生成小计。此外，CUBE 扩展将生成 GROUP BY 子句中指定的所有组合的分组列的小计。

下面说明了 CUBE 扩展的语法

SELECT 
    c1, c2, AGGREGATE_FUNCTION(c3)
FROM
    table_name
GROUP BY CUBE(c1 , c2);
Code language: SQL (Structured Query Language) (sql)

在此语法中，我们在 CUBE 中指定了两个列。此语句创建两个小计组合。通常，如果你的 CUBE 中列出了 n 个列，该语句将创建 2ⁿ 个小计组合。

SQL CUBE 示例

我们将重新使用 ROLLUP 教程中创建的 inventory 表。

带有单列的 SQL CUBE 示例

下面的语句使用 SUM() 函数和 GROUP BY 子句查找每个仓库的总库存

SELECT
   warehouse,
   SUM(quantity)
FROM
   inventory
GROUP BY
   warehouse;
Code language: SQL (Structured Query Language) (sql)

如果你想了解所有仓库的总库存，可以使用 GROUP BY 子句中的 CUBE 扩展，如下所示

SELECT
   warehouse,
   SUM(quantity)
FROM
   inventory
GROUP BY
   CUBE(warehouse)
ORDER BY
   warehouse;  
Code language: SQL (Structured Query Language) (sql)

在此示例中，CUBE 扩展在 warehouse 列中添加了含有空值的总库存行。效果与 ROLLUP 函数相同。为了使输出更具可读性，你可以使用 COALESCE() 函数，如下所示

SELECT
   COALESCE(warehouse,'All warehouses'),
   SUM(quantity)
FROM
   inventory
GROUP BY
   CUBE(warehouse)
ORDER BY
   warehouse;
Code language: SQL (Structured Query Language) (sql)

带有多列的 SQL CUBE 示例

下面的语句查找按仓库和产品划分的总库存

SELECT
   warehouse,
   product,
   SUM(quantity)
FROM
   inventory
GROUP BY
   warehouse,product
ORDER BY
   warehouse,
   product;
Code language: SQL (Structured Query Language) (sql)

在使用 CUBE 函数时，该查询执行四个小计

SELECT
   warehouse,
   product,
   SUM(quantity)
FROM
   inventory
GROUP BY
   CUBE(warehouse,product)
ORDER BY
   warehouse,
   product;
Code language: SQL (Structured Query Language) (sql)

如你所见，在输出中我们有四个小计行

• 第三和第六行显示了 San Francisco 和 San Jose 仓库中所有产品的总库存。product 列中的值为空。
• 第七和第八行显示所有仓库中 Samsung 和 iPhone 等产品的总库存。因此，warehouse 列中的值为空。

最后一列是显示所有仓库总库存的总计。

下面的语句使用 COALESCE() 函数将 Null 值替换为更有意义的数据

SELECT
   COALESCE(warehouse, '...All Warehouses') warehouse,
  COALESCE(product, '...All Products') product,
   SUM(quantity) 
FROM
   inventory
GROUP BY
   CUBE(warehouse,product)
ORDER BY
   warehouse,
   product; 
Code language: SQL (Structured Query Language) (sql)

制作交叉表报表

下面的查询使用 CUBE 扩展从示例数据库中的  employees 表中检索数据以创建交叉表报表

SELECT
   COALESCE(department_name, '-') department,
   COALESCE(job_title,'-') job,
   COUNT(*) ,
   SUM(salary) salary
FROM
   employees
INNER JOIN departments USING (department_id)
INNER JOIN jobs USING (job_id)
GROUP BY
   CUBE(department_name,job_title)
ORDER BY
   department_name ASC NULLS LAST;Code language: SQL (Structured Query Language) (sql)

结果如下所示

在本教程中，你已学习了如何使用 SQL CUBE 扩展功能在查询输出中生成小计。