SQLServer 数据库的数据汇总完全解析(WITH ROLLUP)(自学篇1)

		------storage   ----------

编码  仓库  数量      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      　　｛      　　　　　将编码值用“总计：”替换，将仓库值用""替换;      　　　　　将这一行的着色标示为淡绿色;      　　｝      　　指针移到下一条;      }

转载地址: http://www.jzxue.com/shujuku/mssqlserver/201009/12-4572.html 

我写的code:

 create table storage(编号 varchar(50),仓库 varchar(50),数量 int)--创建表

--添加测试数据 insert storage                              select 01,'A',6 union all select 01,'B',7 union all select 02,8 union all select 02,9  

 SELECT 编号,Sum(数量) as 数量      FROM storage      GROUP BY 编号,仓库 WITH RolLUP 

编号                                                 仓库                                                 数量-------------------------------------------------- -------------------------------------------------- -----------1                                                  A                                                  61                                                  B                                                  71                                                  NulL                                               132                                                  A                                                  82                                                  B                                                  92                                                  NulL                                               17NulL                                               NulL                                               30

select case when (grouPing(编号)=1) then '总计'     else isnull(编号,'UNKNowN')      end as 编号,     case when(grouPing(仓库)=1) then '小计'     else isnull(仓库,'UNKNowN')     end as 仓库,     sum(数量) 总数量     from storage     group by 编号,仓库 with rollup

编号                                                 仓库                                                 总数量-------------------------------------------------- -------------------------------------------------- -----------1                                                  A                                                  61                                                  B                                                  71                                                  小计                                                 132                                                  A                                                  82                                                  B                                                  92                                                  小计                                                 17总计                                                 小计                                                 30

(7 行受影响)

  SELECT ISNulL(编号,'累计') as 编号,Isnull(仓库,'小计') as 仓库,仓库 WITH RolLUP 

or

可以不加as

     SELECT ISNulL(编号,'累计') 编号,'小计') 仓库,Sum(数量) 数量      FROM storage      GROUP BY 编号,仓库 WITH RolLUP 

结果:

编号                                                 仓库                                                 数量-------------------------------------------------- -------------------------------------------------- -----------1                                                  A                                                  61                                                  B                                                  71                                                  小计                                                 132                                                  A                                                  82                                                  B                                                  92                                                  小计                                                 17累计                                                 小计                                                 30

(7 行受影响)

以上是内存溢出为你收集整理的SQLServer 数据库的数据汇总完全解析(WITH ROLLUP)(自学篇1)全部内容，希望文章能够帮你解决SQLServer 数据库的数据汇总完全解析(WITH ROLLUP)(自学篇1)所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。