用ROLLUP进行分类数据统计(一) - tfc2005的博客

作者在 2006-10-11 00:47:00 发布以下内容

作者：佚名来源：InterNet 加入时间：2005-1-31

通常，我们在写web应用程序中，会经常用到分类数据统计的功能。在一个电子商务网站中，我们往往对销售的每类商品的销售额，销售的数量要进行分类统计。那么，在asp.net中，我们如何用datagrid，一方面显示数据库中的数据，一方面又能按类别对数据进行分类统计呢？方法应该有很多种，但在这里，想介绍一种容易被人遗忘的方法，那就是使用MS　SQL SERVER中的roll up语句了。

让我们先来介绍下ms sql server中的roll up语句。Roll up语句，在对统计的数据既要进行分类求和，又要求其总和时，是十分有用的。Roll up语句必须配合group by使用，举个例子，比如在northwind数据库中，为了返回同一目录下的产品总价格，和库存量，可以使用如下sql 语句：

SELECT 
      CategoryName,  
      SUM(UnitPrice) as UnitPrice,  
      SUM(UnitsinStock) as UnitsinStock  
FROM Products 
    INNER JOIN Categories On 
   Products.CategoryID = Categories.CategoryID 
GROUP BY CategoryName

返回的结果如下

　　

而如果想既列出分类中所有产品，又能分类统计出每个分类的产品，价格统计总数，那么roll up就大有用武之地了。我们想达到的效果可以用下图表示：

请注意上表中黄色的部分，比如，

表示meat/poultry这个分类中的产品总价格和数量，这就达到了分类统计的目的，最后一行

表示所有分类中产品的总价格和总的数量。可以看出，roll up的实质就是按列，既对分类求和又求所有分类的总和，但要注意一点，roll up在做分类统计时，会在某些字段插入空值，比如，在

中，rollup在做分类统计时，首先是会以下面的形式出现

那么如何将这些null值替换掉呢，可以使用下面的语句：

SELECT
  CASE 
    WHEN (Grouping(CategoryName)=1) THEN 'MainTotal' 
    ELSE CategoryName 
  END AS CategoryName, 

  CASE 
    WHEN (Grouping(ProductName)=1) THEN 'SubTotal' 
    ELSE Productname 
  END AS ProductName,  

  Sum(UnitPrice) as UnitPrice,  
  Sum(UnitsinStock) as UnitsInStock  

FROM Products 
  INNER JOIN Categories On 
       Products.CategoryID = Categories.CategoryID 
GROUP BY CategoryName, ProductName WITH ROLLUP

其中，使用case when..else 的语句时，当productname一列中遇到有null值时，用"subtotal"来代替,当categoryname一列中遇到有null值时,用"maintotal"值来代替。