现有表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”值?哈,别急,这几行正是我们所要的汇总数据行,不难看出:
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
{
将编码值用“总计:”替换,将仓库值用""替换;
将这一行的着色标示为淡绿色;
}
指针移到下一条;
} |
分享到:
相关推荐
总结起来,WITH ROLLUP是SQL Server中一种强大的数据汇总工具,它能帮助开发者快速生成分层的汇总报告,极大地提高了数据分析的效率。掌握WITH ROLLUP的使用,对于处理复杂的数据汇总场景尤其有用。在实际工作中,...
`CUBE`和`ROLLUP`是`WITH`子句中的两个特殊运算符,它们主要用于多维度数据分析和聚合操作,通常在`GROUP BY`语句中配合使用。 1. **CUBE运算符**: `CUBE`生成的结果集包含了所有可能的子集,即所选列中值的所有...
小编对工作中遇到的用数据库语句进行分类汇总的查询信息的信息。压缩包中附件相关的数据库和 sql脚本。 详细的思路过程见博客:https://blog.csdn.net/aiming66/article/details/80717802
在SQLServer数据库中,复杂查询通常涉及到多个表的联接(JOIN)、子查询、聚合函数(GROUP BY)、分组层次(ROLLUP/CUBE/GROUPING SETS)以及窗口函数(Window Functions)。这些技术使得我们可以从庞大的数据集中...
在SQL Server中,分组统计是一项基础且重要的功能,它允许我们按照特定列的值对数据进行聚合,以便分析和汇总信息。`GROUP BY`语句是实现这一目标的关键,而`WITH ROLLUP`是扩展这个功能的一种方法,特别适用于生成...
下面将详细介绍在SQL Server 2000中如何进行数据库、表以及用户自定义数据类型的创建,并讨论如何插入、删除、更新数据,以及使用各种约束、聚合函数和时间函数。 1. 创建数据库、表、用户定义的数据类型 创建...
使用WITH ROLLUP:在group分组字段的基础上进行统计数据。 函数COALESCE(expression_1,…,expression_n)依次参考各参数表达式,遇到非null值即停止并返回该值。如果所有的表达式都是空值,最终将返回一个空值。 代码...
SELECT语句是最基本的SQL语句,用于从数据库表中检索数据。Oracle和SqlServer的SELECT语句语法类似,但是也存在一些差异。 Oracle: ``` SELECT [/*+ optimizer_hints*/] [ALL | DISTINCT] select_list FROM {table...
- **SQL Server:** SQL Server提供了类似的SELECT语句结构,但并不支持`START WITH ... CONNECT BY` 子句。为了实现层次结构查询,可以使用递归公共表表达式(Recursive CTEs)。 ```sql SELECT select_list ...
在数据库管理中,当我们需要对某些特定字段的数据进行汇总统计时,经常会使用到`GROUP BY`。 #### Rollup 与 Cube 的概念介绍 - **Rollup**:用于生成包含所有可能的汇总级别的分组结果集。例如,在两个字段`部门`...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
通过使用`ROLLUP`,可以在结果集中自动生成各种级别上的汇总数据,而无需额外编写复杂的SQL语句。 #### 二、基本用法与示例 ##### 2.1 基础示例:统计各在职状态的人数 在Oracle数据库环境下,如果想要统计每个...
本书适合专业数据库开发者、BI开发者、DBA和以SQL Server作为后台数据库的一般应用程序开发者,读者可以通过书中的最佳实践、高级技巧和代码示例来掌握这门复杂的编程语言,以切合实际的方案来解决复杂的实际问题。...
T-SQL(Transact-SQL)是微软SQL Server数据库管理系统中用于数据操作和管理的结构化查询语言,它是SQL标准的一个扩展。本套书籍的第二部分主要关注T-SQL查询,是数据库管理员、开发人员和数据分析人员提升SQL技能的...
在SQL Server 2008版本中,T-SQL的重要性不言而喻,它不仅是数据查询的基础,也是数据库开发、维护和性能优化的核心。 这本书详细讲解了以下几个关键知识点: 1. **T-SQL基础语法**:涵盖SELECT、FROM、WHERE、...
例如,当使用 SQL Server 2000 Desktop Engine 或 Microsoft 数据引擎 (MSDE) 1.0 时,数据库大小将限制在 2 GB。 #### 三、其他重要注意事项 - **网络数据包大小**:网络数据包大小是指应用程序与数据库引擎之间...
本文旨在通过实际场景下的性能测试来对比SQL Server、MySQL以及StarRocks这三种数据库管理系统在处理相同查询时的表现。测试环境分别为: - **SQL Server**:209莱芜农引WinServer系统,8核2.1GHz CPU,32GB内存。 ...
SQL Server是微软公司推出的一款关系型数据库管理系统,广泛应用于企业数据存储、管理和分析。本资料“sql server书籍技巧.rar”涵盖了多个SQL Server使用中的关键知识点,包括时间日期处理、字符串操作、排序与分页...