玩转数据库之 Group by Grouping

简介: 有的时候我们要从数据库里把数据组织成树结构再展现到页面上 像下面这样 今天我们用Group 和Grouping实现它,并总结一下它俩。 先看一下概念,再用代码一点一点去理解它们,最后我会给出完整的代码 Group By : 语句用于结合合计函数,根据一个或多个列对结果集进行分组。

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

像下面这样

今天我们用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

  

 

 

 

 

 

 

 

目录
相关文章
|
6月前
|
SQL 关系型数据库 MySQL
MySQL数据库——SQL优化(2/3)-order by 优化、group by 优化
MySQL数据库——SQL优化(2/3)-order by 优化、group by 优化
58 0
|
7月前
|
存储 SQL Apache
阿里云数据库内核 Apache Doris 基于 Workload Group 的负载隔离能力解读
阿里云数据库内核 Apache Doris 基于 Workload Group 的负载隔离能力解读
阿里云数据库内核 Apache Doris 基于 Workload Group 的负载隔离能力解读
|
7月前
|
数据库
sqlserver数据库学习感悟(1)----关于group by
sqlserver数据库学习感悟(1)----关于group by
47 0
|
数据库
达梦数据库,第二章:报错不是 GROUP BY 表达式
达梦数据库,第二章:报错不是 GROUP BY 表达式
493 0
达梦数据库,第二章:报错不是 GROUP BY 表达式
|
关系型数据库 MySQL 数据库
【黄啊码】MySQL入门—5、数据库小技巧:单个列group by就会,多个列呢?
【黄啊码】MySQL入门—5、数据库小技巧:单个列group by就会,多个列呢?
122 0
【黄啊码】MySQL入门—5、数据库小技巧:单个列group by就会,多个列呢?
|
21天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
34 1
|
23天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
36 4
|
1月前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
171 1
|
1月前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
89 2