有的时候我们要从数据库里把数据组织成树结构再展现到页面上
像下面这样
今天我们用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
|
本文转自lpxxn博客园博客，原文链接：http://www.cnblogs.com/li-peng/p/3298303.html，如需转载请自行联系原作者
