sqlserver Group by Grouping

有的时候我们要从数据库里把数据组织成树结构再展现到页面上

像下面这样

今天我们用Group 和Grouping实现它，并总结一下它俩。

先看一下概念,再用代码一点一点去理解它们，最后我会给出完整的代码

Group By : 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

Grouping :指示是否聚合 GROUP BY 列表中的指定列表达式。 在结果集中，如果 GROUPING 返回 1 则指示聚合；

                返回 0 则指示不聚合。 如果指定了 GROUP BY，则 GROUPING 只能用在 SELECT <select> 列表、HAVING 和 ORDER BY 子句中。

ROLLUP ：生成简单的 GROUP BY 聚合行以及小计行或超聚合行，还生成一个总计行。

让我们先建一个数据库，并添加一些数据

use master  go if exists( select  1 from  sysdatabases where  name  = 'MyGroupDB' )      ALTER  DATABASE  MyGroupDB SET  SINGLE_USER with  ROLLBACK  IMMEDIATE       drop  database  MyGroupDB go   create  database  MyGroupDB go use MyGroupDB go   create  Table  Category (      Category_ID int  identity(1,1),      Category_Name varchar (100) ) go create  Table  Product (      Product_ID int  identity(1,1),      CategoryID int  ,      Product_Name varchar (100) ) go insert  into  Category values ( '手机' ) insert  into  Category values ( '台式机' ) insert  into  Category values ( '数码相机' ) go   insert  into  Product values (1, '诺基亚' ) insert  into  Product values (1, '三星' ) insert  into  Product values (1, '苹果' )   insert  into  Product values (2, 'HP' ) insert  into  Product values (2, 'IBM' ) insert  into  Product values (2, 'Dell' )     insert  into  Product values (3, '佳能' ) insert  into  Product values (3, '尼康' ) insert  into  Product values (3, '索尼' ) go

看一下它们的数据

select  *  from  Category left  join  Product on  Category_ID = CategoryID

我们把它们用Group By分一下组

select Category_ID ,                
        Category_Name,        
        CategoryID,        
        Product_Name    
from Category 
left join Product on Category_ID = CategoryID
group by Category_ID ,CategoryID,Category_Name,Product_Name

我们看到这样和没有分组时展现的数据是一样的，让我们加上 ROLLUP 加上合计行

select  Category_ID ,                         Category_Name,               CategoryID,              Product_Name    from  Category left  join  Product on  Category_ID = CategoryID group  by  Category_ID ,CategoryID,Category_Name,Product_Name  with  rollup

我们看到了好多NULL数据，而且很有规律

这些规律我们可以用Grouping 看到

select  Category_ID ,          GROUPING (Category_ID) as  Category_IDGP,                              Category_Name,          GROUPING (Category_Name) as  Category_NameGP,              CategoryID,          GROUPING (CategoryID) as  CategoryIDGP,                Product_Name,          GROUPING (Product_Name) as  Product_NameGP from  Category left  join  Product on  Category_ID = CategoryID group  by  Category_ID ,Category_Name,CategoryID,Product_Name with  rollup

 你会发现那些Null值就是Grouping 为1的时候

最后一行的合计是Categrory_ID的，我们不需要，CategoryID的合计我们也不需要我们要怎么去掉它们呢，在having 里

select  Category_ID ,          GROUPING (Category_ID) as  Category_IDGP,              CategoryID,          GROUPING (CategoryID) as  CategoryIDGP,                Category_Name,          GROUPING (Category_Name) as  Category_NameGP,          Product_Name,          GROUPING (Product_Name) as  Product_NameGP from  Category left  join  Product on  Category_ID = CategoryID group  by  Category_ID ,Category_Name,CategoryID,Product_Name with  rollup having  GROUPING (Category_ID)=0  and  GROUPING (CategoryID)=0

这样的结果 我们看到只有Product_Name的Grouping有为1 了

我们就是用它去实现这棵树

select case  GROUPING (Product_Name) when  1 then  Category_Name  else  ''  end  as  Category_Name, case  GROUPING (Product_Name) when  0 then  Product_Name else  ''  end  as  Product_Name from  Category left  join  Product on  Category_ID = CategoryID group  by  Category_ID ,Category_Name,CategoryID,Product_Name with  rollup having  GROUPING (Category_ID)=0  and  GROUPING (CategoryID)=0 order  by  Category_ID ,Product_Name

下面是完整的代码

use master  go if exists( select  1 from  sysdatabases where  name  = 'MyGroupDB' )      ALTER  DATABASE  MyGroupDB SET  SINGLE_USER with  ROLLBACK  IMMEDIATE       drop  database  MyGroupDB go   create  database  MyGroupDB go use MyGroupDB go   create  Table  Category (      Category_ID int  identity(1,1),      Category_Name varchar (100) ) go create  Table  Product (      Product_ID int  identity(1,1),      CategoryID int  ,      Product_Name varchar (100) ) go insert  into  Category values ( '手机' ) insert  into  Category values ( '台式机' ) insert  into  Category values ( '数码相机' ) go   insert  into  Product values (1, '诺基亚' ) insert  into  Product values (1, '三星' ) insert  into  Product values (1, '苹果' )   insert  into  Product values (2, 'HP' ) insert  into  Product values (2, 'IBM' ) insert  into  Product values (2, 'Dell' )     insert  into  Product values (3, '佳能' ) insert  into  Product values (3, '尼康' ) insert  into  Product values (3, '索尼' ) go     select  *  from  Category left  join  Product on  Category_ID = CategoryID --------------------------------------------------------   select  Category_ID ,                         Category_Name,               CategoryID,              Product_Name    from  Category left  join  Product on  Category_ID = CategoryID group  by  Category_ID ,CategoryID,Category_Name,Product_Name  with  rollup   -------------------------------------------------------- select  Category_ID ,          GROUPING (Category_ID) as  Category_IDGP,                              Category_Name,          GROUPING (Category_Name) as  Category_NameGP,              CategoryID,          GROUPING (CategoryID) as  CategoryIDGP,                Product_Name,          GROUPING (Product_Name) as  Product_NameGP from  Category left  join  Product on  Category_ID = CategoryID group  by  Category_ID ,Category_Name,CategoryID,Product_Name with  rollup   ---------------------- select  Category_ID ,          GROUPING (Category_ID) as  Category_IDGP,              CategoryID,          GROUPING (CategoryID) as  CategoryIDGP,                Category_Name,          GROUPING (Category_Name) as  Category_NameGP,          Product_Name,          GROUPING (Product_Name) as  Product_NameGP from  Category left  join  Product on  Category_ID = CategoryID group  by  Category_ID ,Category_Name,CategoryID,Product_Name with  rollup having  GROUPING (Category_ID)=0  and  GROUPING (CategoryID)=0   -------------------------   select case  GROUPING (Product_Name) when  1 then  Category_Name  else  ''  end  as  Category_Name, case  GROUPING (Product_Name) when  0 then  Product_Name else  ''  end  as  Product_Name from  Category left  join  Product on  Category_ID = CategoryID group  by  Category_ID ,Category_Name,CategoryID,Product_Name with  rollup having  GROUPING (Category_ID)=0  and  GROUPING (CategoryID)=0 order  by  Category_ID ,Product_Name