利用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"值来代替。

SELECT     proccodename,CASE
    WHEN (Grouping(PU_InBillItem.procode)=1) THEN 'SubTotal'
    ELSE PU_InBillItem.procode
  END AS procode,count(0) as num,sum(PIIQty) as PiiQty,sum(PIIAmt) as PiiAmt,
           sum(PIIInQty) as PIIInQty,sum(PIIUnQty) as PIIUnQty
FROM         PU_InBillItem
left join cf_product on PU_InBillItem.procode=cf_product.procode
where  proccodename!=''
group by proccodename,PU_InBillItem.procode with rollup