`
kingsui
  • 浏览: 192697 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

SQL Server数据库的数据汇总完全解析(WITH ROLLUP)

阅读更多

现有表A,内容如下:

编码   仓库    数量 
01      A       6 
01      B       7 
02      A       8 
02      B       9

现在想按编码查询出这种格式:

01      A       6 
01      B       7 
汇总小计:     13 
02      A       8 
02      B       9 
汇总小计:     17

问:该如何实现?

乍一看,好像很容易,用group by好像能实现?但仔细研究下去,你又会觉得group by也是无能为力,总欠缺点什么,无从下手。那么,到底该如何做呢?别急,SQL Server早就帮我们做好了,下面,跟我来。

首先,让我们来看一段话:

在生成包含小计和合计的报表时,ROLLUP 运算符很有用。ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集。

CUBE 运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展,事实数据即记录个别事件的数据。扩展建立在用户打算分析的列上。这些列被称为维。多维数据集是一个结果集,其中包含了各维度的所有可能组合的交叉表格。

CUBE 运算符在 SELECT 语句的 GROUP BY 子句中指定。该语句的选择列表应包含维度列和聚合函数表达式。GROUP BY 应指定维度列和关键字 WITH CUBE。结果集将包含维度列中各值的所有可能组合,以及与这些维度值组合相匹配的基础行中的聚合值。

CUBE 和 ROLLUP 之间的区别在于:

CUBE 生成的结果集显示了所选列中值的所有组合的聚合。

ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

看完以上的这段话,悟出了什么没有?如果没有,那么……嘿嘿,你的悟性还不够哟,离“三花棸顶”还早着呢:)。接下来我们再看一段(注意哟,答案马上就揭晓了):

SELECT 编码, 仓库, Sum(数量) as 数量 
FROM A 
GROUP BY 编码, 仓库 WITH ROLLUP

--关键就是后面的WITH ROLLUP

--当然,你也可以用WITH CUBE,但是结果会有点不大一样

可能看完上面这段你还是觉得“云里雾里”,摸不着头脑。实在不明白也没关系,自己动手做。

首先:建一个上面所说的A表,输入几行数据;

接着:打开你的SQL Server查询分析器,连上包含你上面所建A表的服务器,选择包含该表的数据库;

然后:Copy上面这段SQL 语句,Paste到查询分析器中,按F5,怎么样?看到下面出来了什么?是不是和我下面的一样?

编码        仓库       数量 
01            A          6 
01            B          7 
01        NULL        13 
02            A           8 
02            B           9 
02        NULL       17 
NULL        NULL       30

--如果你用的是WITH CUBE,结果集的后面还会多出两条(如果你也只是输入示例中的几行数据的话):

NULL          A          14 
NULL          B          16

咦!奇怪,结果中怎么有那么多“NULL”值?哈,别急,这几行正是我们所要的汇总数据行,不难看出:

01 NULL 13正是对编码为01的所有仓库中的数量的汇总;02 NULL 17是对编码为02的所有仓库的数量的汇总;

NULL NULL 30是对所有资料行数量的汇总。

如何?答案出来了吧?是不是很简单呢?当然,上面还有点美中不足,那就是有好多“NULL”的存在。如何去掉这些无意义的NULL呢?下面我们再进行优化。

1、用Grouping替换NULL值

SELECT CASE WHEN (GROUPING(编码) = 1) THEN 'ALL' 
ELSE ISNULL(编码, 'UNKNOWN') 
END AS 编码, 
CASE WHEN (GROUPING(仓库) = 1) THEN 'ALL' 
     ELSE ISNULL(仓库, 'UNKNOWN') 
END AS 仓库, 
SUM(数量) AS 数量 
FROM A 
GROUP BY 编码, 仓库 WITH ROLLUP

--适当的运用Case函数

结果我这里就不写了,就是把上面的“NULL”值全部换成“ALL”字符串

2、利用程序做进一步的优化

//通常为了显示上的需要,我们必须对以上SQL语句生成的结果做一些优化,下面给出自然语言描述:

WHILE(未到达最后一条记录){ 
  IF 编码值不为ALL而仓库值为ALL 
  { 
     将编码值用“小计:”替换,将仓库值用""替换; 
     将这一行的颜色标示为灰色; 
  } 
  ELSE 编码值为ALL仓库值也为ALL 
  { 
     将编码值用“总计:”替换,将仓库值用""替换; 
     将这一行的着色标示为淡绿色; 
  } 
  指针移到下一条; 
}
分享到:
评论

相关推荐

    SQLServer 数据库的数据汇总完全解析(WITH ROLLUP)

    总结起来,WITH ROLLUP是SQL Server中一种强大的数据汇总工具,它能帮助开发者快速生成分层的汇总报告,极大地提高了数据分析的效率。掌握WITH ROLLUP的使用,对于处理复杂的数据汇总场景尤其有用。在实际工作中,...

    Sql学习第四天——SQL 关于with cube,with rollup和grouping解释及演示

    `CUBE`和`ROLLUP`是`WITH`子句中的两个特殊运算符,它们主要用于多维度数据分析和聚合操作,通常在`GROUP BY`语句中配合使用。 1. **CUBE运算符**: `CUBE`生成的结果集包含了所有可能的子集,即所选列中值的所有...

    如何在sqlserver中进行分类汇总-附件sql server 2005实例数据库和脚本

    小编对工作中遇到的用数据库语句进行分类汇总的查询信息的信息。压缩包中附件相关的数据库和 sql脚本。 详细的思路过程见博客:https://blog.csdn.net/aiming66/article/details/80717802

    SQLServer数据库复杂查询示例

    在SQLServer数据库中,复杂查询通常涉及到多个表的联接(JOIN)、子查询、聚合函数(GROUP BY)、分组层次(ROLLUP/CUBE/GROUPING SETS)以及窗口函数(Window Functions)。这些技术使得我们可以从庞大的数据集中...

    Sql Server 分组统计并合计总数及WITH ROLLUP应用

    在SQL Server中,分组统计是一项基础且重要的功能,它允许我们按照特定列的值对数据进行聚合,以便分析和汇总信息。`GROUP BY`语句是实现这一目标的关键,而`WITH ROLLUP`是扩展这个功能的一种方法,特别适用于生成...

    SQLServer数据库程序设计说明文档(图书馆系统).doc

    下面将详细介绍在SQL Server 2000中如何进行数据库、表以及用户自定义数据类型的创建,并讨论如何插入、删除、更新数据,以及使用各种约束、聚合函数和时间函数。 1. 创建数据库、表、用户定义的数据类型 创建...

    sql-显示供应商供应零件的汇总列表(with rollup+coalesce)

    使用WITH ROLLUP:在group分组字段的基础上进行统计数据。 函数COALESCE(expression_1,…,expression_n)依次参考各参数表达式,遇到非null值即停止并返回该值。如果所有的表达式都是空值,最终将返回一个空值。 代码...

    Oracle和SqlServer语法区别

    SELECT语句是最基本的SQL语句,用于从数据库表中检索数据。Oracle和SqlServer的SELECT语句语法类似,但是也存在一些差异。 Oracle: ``` SELECT [/*+ optimizer_hints*/] [ALL | DISTINCT] select_list FROM {table...

    ORACLE和SQL Server的语法区别

    - **SQL Server:** SQL Server提供了类似的SELECT语句结构,但并不支持`START WITH ... CONNECT BY` 子句。为了实现层次结构查询,可以使用递归公共表表达式(Recursive CTEs)。 ```sql SELECT select_list ...

    SQL语句中Group BY 和Rollup以及cube用法

    在数据库管理中,当我们需要对某些特定字段的数据进行汇总统计时,经常会使用到`GROUP BY`。 #### Rollup 与 Cube 的概念介绍 - **Rollup**:用于生成包含所有可能的汇总级别的分组结果集。例如,在两个字段`部门`...

    Microsoft SQL Server 2008技术内幕:T-SQL查询(第二卷)

    《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...

    使用ROLLUP函数生成报表的小计、合计

    通过使用`ROLLUP`,可以在结果集中自动生成各种级别上的汇总数据,而无需额外编写复杂的SQL语句。 #### 二、基本用法与示例 ##### 2.1 基础示例:统计各在职状态的人数 在Oracle数据库环境下,如果想要统计每个...

    Microsoft_SQL_Server_2005技术内幕:T-SQL查询.pdf

    本书适合专业数据库开发者、BI开发者、DBA和以SQL Server作为后台数据库的一般应用程序开发者,读者可以通过书中的最佳实践、高级技巧和代码示例来掌握这门复杂的编程语言,以切合实际的方案来解决复杂的实际问题。...

    Microsoft SQL Server 2005技术内幕全套(二):T-SQL查询.part1

    T-SQL(Transact-SQL)是微软SQL Server数据库管理系统中用于数据操作和管理的结构化查询语言,它是SQL标准的一个扩展。本套书籍的第二部分主要关注T-SQL查询,是数据库管理员、开发人员和数据分析人员提升SQL技能的...

    Microsoft SQL SERVER 2008技术内幕 T-SQL查询

    在SQL Server 2008版本中,T-SQL的重要性不言而喻,它不仅是数据查询的基础,也是数据库开发、维护和性能优化的核心。 这本书详细讲解了以下几个关键知识点: 1. **T-SQL基础语法**:涵盖SELECT、FROM、WHERE、...

    SQL Server2000最大容量说明

    例如,当使用 SQL Server 2000 Desktop Engine 或 Microsoft 数据引擎 (MSDE) 1.0 时,数据库大小将限制在 2 GB。 #### 三、其他重要注意事项 - **网络数据包大小**:网络数据包大小是指应用程序与数据库引擎之间...

    sql server ,mysql,starrocks性能对比.docx

    本文旨在通过实际场景下的性能测试来对比SQL Server、MySQL以及StarRocks这三种数据库管理系统在处理相同查询时的表现。测试环境分别为: - **SQL Server**:209莱芜农引WinServer系统,8核2.1GHz CPU,32GB内存。 ...

    sql server书籍技巧.rar

    SQL Server是微软公司推出的一款关系型数据库管理系统,广泛应用于企业数据存储、管理和分析。本资料“sql server书籍技巧.rar”涵盖了多个SQL Server使用中的关键知识点,包括时间日期处理、字符串操作、排序与分页...

Global site tag (gtag.js) - Google Analytics