SQLServer 数据库的数据汇总完全解析(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

      　　｛

      　　　　　将编码值用“总计：”替换，将仓库值用""替换;

      　　　　　将这一行的着色标示为淡绿色;

      　　｝

      　　指针移到下一条;

      }

赞 (0)

相关推荐

  • SQLServer中汇总功能的使用GROUPING,ROLLUP和CUBE

    第一次看到这样的SQL语句,看不懂,其中用到了下面的不常用的 聚集函数:GROUPING 用于汇总数据用的运算符: ROLLUP SELECT CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE  '(Total)' END AS AllCustomersSummary, CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END AS IndividualCustome

  • SQL Server页类型汇总+疑问汇总

    SQL Server页类型汇总+疑问汇总

    SQL Server中包含多种不同类型的页,来满足数据存储的需求.不管是什么类型的页,它们的存储结构都是相同的.每个数据文件都包含相当数量的由8KB组成的页,即每页有8192bytes可用,每页都有96byte用于页头的存储,剩下的空间 才用来存储实际的数据,在页的最后是数据行偏移数组,也可以叫"页槽"数组,我们可以把一个页看做是有一个个方格的书橱,哪行数据占用了哪个槽,都在页尾的位置进行标示,并且页尾数组的写入顺序是倒叙的,这样就可以有效的利用页空间. 由此可以预见,页面上的&quo

  • SQLServer与Oracle常用函数实例对比汇总

    一.数学函数 1.绝对值 复制代码 代码如下: S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) 复制代码 代码如下: S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual 3.取整(小) 复制代码 代码如下: S:select floor(-1.001) value O:select floor(-1.001) value from

  • SQL Server数据汇总五招轻松搞定

    本文我们将讨论如何使用GROUPBY子句来汇总数据. 使用单独列分组 GROUP BY子句通过设置分组条件来汇总数据,在第一个例子中,我在数据库AdventureWork2012中的表 Sales.SalesOrderDetail.中的一列上进行数据分组操作.这个例子以及其他例子都使用数据库AdventureWorks2012,如果你想使用它运行我的代码,你可以点击下载. 下面是第一个示例的源码,在CarrierTrackingNumber列上使用group by子句进行数据分组操作 USE A

  • SQL Server优化50法汇总

    查询速度慢的原因很多,常见如下几种:1.没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷)2.I/O吞吐量小,形成了瓶颈效应.3.没有创建计算列导致查询不优化.4.内存不足5.网络速度慢6.查询出的数据量过大(可以采用多次查询,其他的方法降低数据量)7.锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷)8.sp_lock, sp_who, 活动的用户查看,原因是读写竞争资源.9.返回了不必要的行和列 10.查询语句不好,没有优化可以通过如下方法来优化查询 :1.把数据.日

  • SQLServer 数据库的数据汇总完全解析(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早就帮我们做好了,下面,跟我来. 首先,让我们来看一段话: 在生成包含小计和合计的报表

  • sqlserver数据库导入数据操作详解(图)

    sqlserver数据库导入数据操作详解(图)

    Microsoft SQL Server Management Studio是SQL SERVER的客户端工具,相信大家都知道.我不知道大伙使用导入数据的情况怎么样,反正我最近是遇到过.主要是因为没有远程数据库服务器的权限,而需要测试新修改的内容对旧数据的冲突.因为流程改变,免不了需要修改数据来适应新的变化.所以需要在测试环境里面去模拟真实环境的数据.当时还搞笑,直接是粘贴到EXCEL,然后再复制到数据库.对于一般来说,这种方式也可以,但是对于一些特殊的字符,如果直接粘贴到EXCEL里面的话,并

  • c#连接sqlserver数据库、插入数据、从数据库获取时间示例

    c#连接sqlserver.插入数据.从数据库获取时间 复制代码 代码如下: using System;using System.Data.SqlClient; namespace Test{    //连接数据库    public class Connection    {        private static string connectionString =            "Server = 192.168.1.222;" +            "D

  • 在SQLserver数据库之间进行传表和传数据的图文教程

    在SQLserver数据库之间进行传表和传数据的图文教程

    一.如何传表? 1.简单的生成脚本,修改数据库名字完成传表(这里以Person表和People做示例) Person里已有表 生成脚本文件 直接下一步 选择要传的文件 文件路径自选,另存为ANSI文本,然后下一步 直接下一步 脚本生成 找到并打开此文件 USE [Person]修改成 USE [People] F5执行后表就传过去了,但数据传没传过去,下一步介绍传数据 二.如何传数据? 1.将数据导出到另一个数据库 People数据库中没有数据 从Person数据库中传入数据 身份验证后,下拉框

  • .NET多种数据库大数据批量插入、更新（支持SqlServer、MySql、PgSql和Oracle）

    .NET多种数据库大数据批量插入、更新（支持SqlServer、MySql、PgSql和Oracle）

    功能介绍 (需要版本5.0.45) 大数据操作ORM性能瓶颈在实体转换上面,并且不能使用常规的Sql去实现 当列越多转换越慢,SqlSugar将转换性能做到极致,并且采用数据库最佳API 操作数据库达到极限性能 功能用法 BulkCopy性能远强于现有市场的 ORM框架,比 EFCore Efcore.Bulkextension快30% BulkUpdate吊打现有所有框架是EFCoreEfcore.Bulkextension 2-3倍之快 //插入 100万 10秒不到 db.Fastest<

  • .NET多种数据库大数据批量插入、更新(支持SqlServer、MySql、PgSql和Oracle)

    .NET多种数据库大数据批量插入、更新(支持SqlServer、MySql、PgSql和Oracle)

    目录 功能介绍(需要版本5.0.45) 功能用法 数据库支持 性能测试 使用疑问 老版本 源码下载: 功能介绍 (需要版本5.0.45) 大数据操作ORM性能瓶颈在实体转换上面,并且不能使用常规的Sql去实现 当列越多转换越慢,SqlSugar将转换性能做到极致,并且采用数据库最佳API 操作数据库达到极限性能 功能用法 BulkCopy性能远强于现有市场的 ORM框架,比 EFCore Efcore.Bulkextension快30% BulkUpdate吊打现有所有框架是EFCoreEfco

  • sqlserver数据库优化解析（图文剖析）

    sqlserver数据库优化解析（图文剖析）

    下面通过图文并茂的方式展示如下: 一.SQL Profiler 事件类 Stored Procedures\RPC:Completed TSQL\SQL:BatchCompleted 事件关键字段 EventSequence.EventClass.SPID.DatabaseName.Error.StartTime.TextData. HostName.ClientProcessID.ApplicationName. CPU.Reads.Writes.Duration.RowCounts     

  • sqlserver数据库大型应用解决方案经验总结

    sqlserver数据库大型应用解决方案经验总结

    随着互联网应用的广泛普及,海量数据的存储和访问成为了系统设计的瓶颈问题.对于一个大型的互联网应用,每天百万级甚至上亿的PV无疑对数据库造成了相当高的负载.对于系统的稳定性和扩展性造成了极大的问题. 一.负载均衡技术负载均衡集群是由一组相互独立的计算机系统构成,通过常规网络或专用网络进行连接,由路由器衔接在一起,各节点相互协作.共同负载.均衡压力,对客户端来说,整个群集可以视为一台具有超高性能的独立服务器. 1.实现原理实现数据库的负载均衡技术,首先要有一个可以控制连接数据库的控制端.在这里,它截

  • asp.net+ajax+sqlserver自动补全功能实现解析

    代码下载 说明:数据库连接字符串在web.config文件中,为方便运行使用的是官方的Northwind数据库. 参考(向其作者致敬): ² http://www.loveweb8.com/plus/demo.php?aid=57这个例子是html源码.利用jquery.autocomplete插件利用js实现了自动补全功能.由于我的需要是结合sqlserver数据库表实现自动补全功能.就下来就是将数据库表转为js数组,自然而然就想到了ajax. ² Asp.NetAjax的两种基本开发模式这篇

  • SQLServer数据库中开启CDC导致事务日志空间被占满的原因

    SQLServer数据库中开启CDC导致事务日志空间被占满的原因

    SQLServer中开启CDC之后,在某些情况下会导致事务日志空间被占满的现象为: 在执行增删改语句(产生事务日志)的过程中提示,The transaction log for database '***' is full due to 'REPLICATION'(数据库"***"的事务日志已满,原因为"REPLICATION"). CDC以及复制的基本原理粗略地讲,对于日志的使用步骤如下: 1,每当基础表(开启了CDC或者replication的表)产生事务性操作

随机推荐