当前位置：Gxlcms > 数据库问题 > 玩转数据库之 Group by Grouping

玩转数据库之 Group by Grouping

时间：2021-07-01 10:21:17 帮助过：2人阅读

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

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

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

看一下它们的数据

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

玩转数据库之 Group by Grouping

标签：

玩转数据库之 Group by Grouping

人气教程排行