SQL Server数据汇总完全解析

SQL Server数据汇总完全解析

文章出处:http://www.cnbolgs.com/bonny.wong
文章作者:Bonny.Wong(让思想飞翔)            
写作时间:2005-03-11                                


  

前言:
        在论坛上经常看到有人问“如何实现数据的分类汇总”,很多的人都是介绍这样或那样的控件来实现,而没有从关系数据库语言(SQL)的本身来考虑实现方法。这里,我就借一个实例来说明如何借助SQL自身强大的功能来实现数据的分类汇总。

问题的提出: 
现有表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(编码) = 1THEN 'ALL'
            
ELSE ISNULL(编码, 'UNKNOWN')
       
END AS 编码,
       
CASE WHEN (GROUPING(仓库) = 1THEN '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
  {
     将编码值用“总计:”替换,将仓库值用
""替换;
     将这一行的着色标示为淡绿色;
  }
  指针移到下一条;
}

//当然,你尽可以发挥你的想象,把表格打扮得漂漂亮亮的,我就不再罗嗦了。

结束语:
        通过上面的讲述,不知道你明白了没有,限于作者的文字表达能力,未解释清楚之处还请见谅。

posted on 2005-03-11 20:07 Bonny.Wong 阅读(2102) 评论(24)  编辑 收藏 收藏至365Key 所属分类: 数据库应用

FeedBack:
# re: 用SQL Server实现数据汇总
2005-03-12 01:34 | CsOver
楼主说得很清楚,我对SQL还研究不深,所以有点“吃了不消化”的感觉!
  
# re: SQL Server数据汇总完全解析
2005-03-12 10:56 | 寒冰如火
楼主说的很好,可惜我不太懂sql. 谢谢!!

但是,我把楼主这一句改了一下放在access的查询分析器中,也的确实现同样的功能。

SELECT A.编码, A.仓库, Sum(A.数量) AS [数量合计]
FROM A
GROUP BY A.编码, A.仓库;

或者,有时这样的工作,我是在excel中完成的。
先执行筛选,然后运用subtotal(range)的函数,进行筛选后的汇总。


  
# re: SQL Server数据汇总完全解析
2005-03-12 11:15 | dgqdiy
你的语句和我的:
(Select 编码,仓库,数量,1 as tmp From 表A
Union All
Select 编码,'汇总小计:',sum(数量),2 as tmp From 表A Group by 编码)
Order by 编码,tmp 结果是一样的!
弊端就是,---排序锁死!---所以不想用这种方式,有没有grid之类的控件,可以解决再排序问题的!

  
# re: SQL Server数据汇总完全解析
2005-03-12 11:33 | dgqdiy
补充:
其实也就是想把.
01 A  6
01 B   7
01   NULL  13
当作一行,一个整体排序,排序时,这一个结构不变,能不能做到呢?
  
# re: SQL Server数据汇总完全解析
2005-03-12 13:25 | zfphere
我这里还有个关于数据横排的:
http://www2.cnblogs.com/3zfp/archive/2004/12/19/79084.html
  
# re: SQL Server数据汇总完全解析
2005-03-12 13:50 | Bonny.Wong(让思想飞翔)
to dgqdiy:
  按編碼排序難道結果不是你上面說的當作一個整體排序嗎?
  對不起,我不是很明白你的意思!

  
# re: SQL Server数据汇总完全解析
2005-03-12 14:12 | James Wong
嗦哈嗦哈
  
# re: SQL Server数据汇总完全解析
2005-03-13 08:49 | GOLDMAN
很简单的,只要看看帮助都知道了
  
# re: SQL Server数据汇总完全解析
2005-03-13 08:59 | yyanghhong
对, 我做报表都是用sql来完成大部分事情, 比如结构或layout, 用excel来做点修饰, 比如border, font啥的, 比如master@details
report, 一般reporting tool用两个report band来绑定两个表, 然后把数据倒到client端来做, 实际上这种方法性能很差, 只是实现起来比较方便而已。对一般小数据系统还可以, 大一点的都会出问题。

顺便介绍一下我做的reporting tool, 完全是按照这种思路来做的


http://www.aspose.com/wiki/default.aspx/MyWiki.SqlEditor

http://www.aspose.com/wiki/default.aspx/MyWiki.AsposeReportOverview
  
# re: SQL Server数据汇总完全解析
2005-04-02 13:26 | dgqdiy
呵呵,我的想法是不可能的,头晕
  
# re: SQL Server数据汇总完全解析
2005-05-09 15:39 | tonghai888@163.com
SELECT CASE WHEN (GROUPING(编码) = 1) THEN '总计'
ELSE ISNULL(编码, 'UNKNOWN')
END AS 编码,
CASE WHEN (GROUPING(仓库) = 1) THEN '小计'
ELSE ISNULL(仓库, 'UNKNOWN')
END AS 仓库,
SUM(数量) AS 数量
FROM A
GROUP BY 编码, 仓库 WITH ROLLUP

/*这样不可以吗,在存储过程中就完全可以实现呀*/
  
# re: SQL Server数据汇总完全解析
2005-05-13 09:38 | susan
请问如何在SQl表中的一列里存储一个数组呢?
谢谢
  
# re: SQL Server数据汇总完全解析
2005-05-13 10:50 | 有点急
不知怎么的,我用这种方法排序,每一列都进行汇总排序,我只要一列进行汇总,我的表有点复杂,用了WHERE 语句
  
# re: SQL Server数据汇总完全解析
2005-05-13 10:53 | 有点急
效果有点用CUBE的样子, 不知怎么回事,请各位高手帮帮

  
# re: SQL Server数据汇总完全解析
2005-05-15 08:51 | 上山砍柴去
to 有点急:
  你讲得不清不楚,怎么帮你?
  
# re: SQL Server数据汇总完全解析
2005-05-16 09:23 | 有点急
我的语句为:
sql= "SELECT CK_NAME as 仓库 , FP_INFO as 发票号,AMOUNT as 数量,oper_date as 日期,MAT_NAME as 物料名称,sum(PLAN_PRICE) as 计划总价 FROM cww where TRANS_NAME='采购入库' and CW_FLAG='2' and OPER_DATE >= '" & date1 & "' and OPER_DATE <= '" & date2 & "' group by CK_NAME,FP_INFO,AMOUNT,oper_date,MAT_NAME with ROLLUP "

我仅仅想对CK_NAME进行汇总,结果对FP_INFO,AMOUNT,oper_date,MAT_NAME 这些也同时进行了分类汇总,弄的我的表乱七八糟
  
# re: SQL Server数据汇总完全解析
2005-05-16 09:40 | 有点急
仓库 发票号 数量 日期 物料名称 计划总价

文具库 2 4-18 白板笔 45.6
文具库 4-18 纸杯 300
文具库 10 4-18 毛笔 5
文具库 2 4-18 银光笔 5.47
文具库 4066994 12 4-19 派克笔 307.692
663.762

散件库B 111784 1 4-19 柴油粗滤 88.82
散件库B 111784 20 4-19 盖板 369.44
458.26

劳保库 144573 50 4-19 小笤帚 90
劳保库 144574 2 4-19 热水瓶 15.2648
105.2648

我要的是这样的结果,只按照仓库进行价格汇总, 而我上面的程序却对各个列都分别进行价格汇总

  
# re: SQL Server数据汇总完全解析
2005-05-16 09:41 | 有点急
发上去,有点乱,有的发票号为空,
  
# re: SQL Server数据汇总完全解析
2005-05-16 11:59 | 上山砍柴去
to 有點急:
with rollup只是按照你group by子句中的條件從後向前依次匯總顯示。
  
# re: SQL Server数据汇总完全解析
2005-05-16 13:14 | 有点急
谢谢你!

  
# re: SQL Server数据汇总完全解析
2005-05-19 10:04 | 有点急
现有这表A,内容如下:
编码 仓库 物品 数量
01 A X 6
01 B X 7
02 A X 8
02 B Y 9
03 A X 2
03 B Y 3
03 C Z 4


能否提供一种最简单的方法,实现如下
--------------------
编码 仓库 物品 数量
01 A X 6
01 B X 7
汇总合计 13

02 A X 8
02 B Y 9
汇总合计 17

03 A X 2
03 B Y 3
03 C Z 4
汇总合计 9

问:该如何实现? 不胜感激

  
# re: SQL Server数据汇总完全解析
2005-05-19 10:09 | 有点急
我用循环语句做出,总觉的很复杂 ,不知道,有没有SQL语句能直接写出
这应该也属于数据汇总的吧 . 中间有空格,总共有4列,请大家帮着看一下 谢谢
  
# re: SQL Server数据汇总完全解析
2005-05-19 11:58 | Bonny.Wong
to 有點急:
給個例子,具體的你自己再參照上文再做修飾
SELECT *
FROM (SELECT 編碼, 倉庫, 名稱, SUM(數量) AS 數量
FROM test2
GROUP BY 編碼, 倉庫, 名稱 WITH rollup) a where not (not a.倉庫 is NUll and a.名稱 is NULL)
  
# re: SQL Server数据汇总完全解析
2005-08-04 10:12 | 菜鸟
我如果想只对某一列汇总但其它列的信息也要比如:
A1 A2 A3 A4
001 dh 男 4
002 bm 女 3
001 dh 男 5
我想要的汇总结果如下
A1 A2 A3 A4
001 dh 男 9
002 bm 女 3
该如何做
posted @ 2005-10-07 19:50  泡面 @ 幸福  阅读(653)  评论(0)    收藏  举报