玩转数据库之 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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

SQL基础-->分组与分组函数

使用group by column1,column2,..按columm1,column2进行分组,即column1,column2组合相同的值为一个组

1302
来自专栏全栈工程师成长之路

深入浅出后端开发(SQL指令笔记)

3377
来自专栏菩提树下的杨过

winform中利用正则表达式得到有效的电话/手机号

private void btnTest_Click(object sender, EventArgs e)         {             ...

1907
来自专栏听雨堂

根据前后的子串求中间串的函数

using System.Text.RegularExpressions;      /// <summary>   /// 给出一个字符串,前面...

2168
来自专栏程序员宝库

MYSQL 业务上碰到的 SQL 问题整理集合

前言 身为一名前端工程师,对于 SQL了解程度并不是很深刻,盘点一些个人工作遇到的问题,给大家普及下知识,以及记录自己如何解决这些问题的。 导航 SELECT ...

3426
来自专栏钟绍威的专栏

当子查询碰上NULLUNIONJOIN总结

情景: 现在有如图两个表,boy和girl,对应着Boy和Girl两个bean,有共同字段id、name,另外boy还有个外键grilfriend(指向girl...

1837
来自专栏个人随笔

MySQL常用函数

聚合函数  统计和分析 数据  使用场景:会和分组结合使用 -- 一旦启用分组,只能获取组的信息,组中成员不能出现在select后 count() 查询某个字...

3507
来自专栏乐沙弥的世界

批量 SQL 之 FORALL 语句

    对PL/SQL而言,任何的PL/SQL块或者子程序都是PL/SQL引擎来处理,而其中包含的SQL语句则由PL/SQL引擎发送SQL语句转交到SQL引擎...

872
来自专栏Java 源码分析

数据库Exists关键字举例

一.问题描述: 查询所有未选择03号课程的学生的姓名 规定使用存在量词 student表: ? grade表: ? 二.思路: 既然是存在量词那么也就是E...

3006
来自专栏数据分析

[数据库基础]——编码标准之格式

代码就像家里的各种物品,格式化就好比对家中物品的排放。家中的物品随便怎么放,物品都不会反对,房子也不会介意,但是物品的排放合理、规整,会让家里变得更具有美感。代...

2918

扫码关注云+社区

领取腾讯云代金券