grouping的用法

hadakanoyichigo 2009-01-01 03:32:04

如题

...全文

1368 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

幸运的意外 2009-01-06

打赏
举报

楼主朋友，关于grouping地用法，可以参考一下邹老大出的那本书，里面讲的比较清楚，另外可以到这个网站去看看，应该会对你有帮助。
http://msdn.microsoft.com/zh-cn/library/ms178544.aspx

JYR520 2009-01-06

打赏
举报

xuexi....

cqsxdb 2009-01-06

打赏
举报

mark

noenoughmemory 2009-01-06

打赏
举报

mark

qizhengsheng 2009-01-06

打赏
举报

-- 示例数据
DECLARE @v TABLE(
学号int, 班级int, 科目nvarchar(10), 成绩int);
INSERT @v
SELECT 1, 1, N'语文', 76 UNION ALL
SELECT 1, 1, N'数学', 87 UNION ALL
SELECT 1, 1, N'英语', 99 UNION ALL
SELECT 2, 1, N'语文', 100 UNION ALL
SELECT 2, 1, N'数学', 56 UNION ALL
SELECT 2, 1, N'英语', 35 UNION ALL
SELECT 3, 2, N'语文', 111 UNION ALL
SELECT 3, 2, N'数学', 90 UNION ALL
SELECT 3, 2, N'英语', 114

SELECT
班级= CASE
WHEN GROUPING(班级) = 1 THEN N'<总计>'
ELSE RTRIM(班级)
END,
科目= CASE
WHEN GROUPING(科目) = 0 THEN 科目
ELSE N'<小计>'
END,
总分= SUM(CASE WHEN 成绩> 0 THEN 成绩END),
人数= SUM(CASE WHEN 成绩> 0 THEN 1 END),
平均= AVG(CASE WHEN 成绩> 0 THEN 成绩END),
优秀率= CASE
WHEN SUM(CASE WHEN 成绩> 0 THEN 1 END) = 0 THEN 0
ELSE CONVERT(decimal(4, 2),
SUM(CASE WHEN 成绩> 80 THEN 1 END) * 1.
/ SUM(CASE WHEN 成绩> 0 THEN 1 END))
END,
及格率= CASE
WHEN SUM(CASE WHEN 成绩> 0 THEN 1 END) = 0 THEN 0
ELSE CONVERT(decimal(4, 2),
SUM(CASE WHEN 成绩> 60 THEN 1 END) * 1.
/ SUM(CASE WHEN 成绩> 0 THEN 1 END))
END,
低分率= CASE
WHEN SUM(CASE WHEN 成绩> 0 THEN 1 END) = 0 THEN 0
ELSE CONVERT(decimal(4, 2),
SUM(CASE WHEN 成绩> 0 AND 成绩< 30 THEN 1 END) * 1.
/ SUM(CASE WHEN 成绩> 0 THEN 1 END))
END,
最高= MAX(成绩),
最低= MIN(CASE WHEN 成绩> 0 THEN 成绩END),
段= SUM(CASE WHEN 成绩>= 90 THEN 1 END),
段= SUM(CASE WHEN 成绩>= 80 AND 成绩< 90 THEN 1 END),
段= SUM(CASE WHEN 成绩>= 70 AND 成绩< 80 THEN 1 END),
段= SUM(CASE WHEN 成绩>= 60 AND 成绩< 70 THEN 1 END),
段= SUM(CASE WHEN 成绩>= 50 AND 成绩< 60 THEN 1 END),
段= SUM(CASE WHEN 成绩>= 40 AND 成绩< 50 THEN 1 END),
段= SUM(CASE WHEN 成绩>= 30 AND 成绩< 40 THEN 1 END),
段= SUM(CASE WHEN 成绩> 0 AND 成绩<= 30 THEN 1 END)
FROM @v v_cjb
GROUP BY 班级, 科目WITH ROLLUP

bluefangxiao 2009-01-06

打赏
举报

up~~~

ws_hgo 2009-01-06

打赏
举报

create TABLE #Good(GoodsID VARCHAR(3),GoodsName VARCHAR(4))

INSERT INTO #Good

SELECT '001','苹果' UNION ALL

SELECT '002','橙' UNION ALL

SELECT '003','梨'

 



create TABLE #GoodIn (GoodsID VARCHAR(3),Qty INT)

INSERT INTO #GoodIn

SELECT '001',10 UNION ALL

SELECT '001',20 UNION ALL

SELECT '002',10 UNION ALL

SELECT '002',20 UNION ALL

SELECT '003',20 UNION ALL

SELECT '003',30

 



create TABLE #GoodOut(GoodsID VARCHAR(3),Qty INT)

INSERT INTO #GoodOut

SELECT '001',1 UNION ALL

SELECT '001',2 UNION ALL

SELECT '002',1 UNION ALL

SELECT '002',2 UNION ALL

SELECT '003',2 UNION ALL

SELECT '003',3

 



create TABLE #GoodStorage (GoodsID VARCHAR(3),Qty INT)

INSERT INTO #GoodStorage

SELECT '001',27 UNION ALL

SELECT '002',27 UNION ALL

SELECT '003',45



/*

GoodsID GoodsName QtyIn(进货) QtyOut(出货) Qty(存货) 

001 苹果 30 3 27 

002 橙 30 3 27 

003 梨 50 5 45

*/



select sum(g1.Qty) '进货',sum(g2.Qty) '出货',sum(g4.Qty) '存货'

from #GoodIn g1 join #GoodOut g2 on g1.GoodsID=g2.GoodsID join

#GoodStorage g4 on g4.GoodsID=g1.GoodsID group by g1.GoodsID





select g3.GoodsID,g3.GoodsName,g3.Qty '进货',g4.Qty '出货',g5.Qty '存货' from

(select g1.GoodsID,g1.GoodsName,sum(g2.Qty) Qty from #Good g1 join #GoodIn g2 

on g1.GoodsID=g2.GoodsID group by g1.GoodsID,g1.GoodsName) g3 join 

(select GoodsID,sum(Qty) Qty from #GoodOut  group by GoodsID) g4 on g4.GoodsID=g3.GoodsID

join (select GoodsID,sum(Qty) Qty from #GoodStorage group by GoodsID) g5 on g4.GoodsID=g5.GoodsID

ws_hgo 2009-01-06

打赏
举报



create table #tb([Name] int ,[Type] varchar(10),YN varchar(10))

insert into #tb values(1,'A','Y') 

insert into #tb values(3,'B','Y') 

insert into #tb values(4,'B','Y' )

insert into #tb values(3,'A','N') 

insert into #tb values(4,'A','Y') 

insert into #tb values(4,'B','Y') 

insert into #tb values(1,'C','N') 

--Name A&Y A&N B&Y B&N C&Y C&N 

select [Name],

sum(case when [Type]=N'A' and YN=N'Y' then 1 else 0 end) 'A&Y',

sum(case when [Type]=N'A' and YN=N'N' then 1 else 0 end) 'A&N',

sum(case when [Type]=N'B' and YN=N'Y' then 1 else 0 end) 'B&Y',

sum(case when [Type]=N'B' and YN=N'N' then 1 else 0 end) 'B&N',

sum(case when [Type]=N'C' and YN=N'Y' then 1 else 0 end) 'C&Y',

sum(case when [Type]=N'C' and YN=N'N' then 1 else 0 end) 'C&N'

from #tb group by [Name]

ws_hgo 2009-01-06

打赏
举报

if object_id('RR') is not null drop table RR

create table RR

(

  product varchar(20),

  [111(shu1)] int,

  [111(shu2)] int,

  [222(shu1)] int,

  [222(shu2)] int 

) 

insert into RR 

select E1.product,E1.shu1 '111(shu1)' ,E1.shu2 '111(shu2)',E2.shu1,E2.shu2 from

(select product,sum(shu1) as shu1,sum(shu2) as shu2 from #EE where shop=111 group by product) E1

join (select product,sum(shu1) as shu1,sum(shu2) as shu2 from #EE where shop=222 group by product) E2 

on E1.product=E2.product

select * from RR

水族杰纶 2009-01-06

打赏
举报

create table   #t(部门   char(1),员工   Nchar(2),工资   int)   

  insert   into   #t   select   'A',N'李',1000   

  insert   into   #t   select   'B',N'王',2000   

  insert   into   #t   select   'A',N'张',3000   

  insert   into   #t   select   'B',N'赵',4000   

    

select   

          部门=case   when   grouping(部门)=0   and   grouping(员工)=1   then   N'合计'   else   部门   end,   

          员工   =   isnull(员工,''),   

          工资   =   sum(工资)

  from   

          #t   

  group   by   

          部门,员工   

  with   rollup   

  having  grouping(部门)=0  

  drop table #t

/*部门   员工   工资          

---- ---- ----------- 

A    李    1000

A    张    3000

合计        4000

B    王    2000

B    赵    4000

合计        6000



(影響 6 個資料列)



*/

/*GROUPING 

是一个聚合函数,它产生一个附加的列，当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0。 



仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相联系的选择列表中才允许分组。 



语法 

GROUPING ( column_name ) 



参数 

column_name 



是 GROUP BY 子句中用于检查 CUBE 或 ROLLUP 空值的列。 



返回类型 

int

参考资料：SQL Server T-SQL帮助*/

starter2009 2009-01-06

打赏
举报

学习了

duanzhi1984 2009-01-05

打赏
举报

k看联机帮助夯

-狙击手- 2009-01-01

打赏
举报

联机帮助一下就知道啦

dawugui 2009-01-01

打赏
举报

GROUPING

是一个聚合函数,它产生一个附加的列，当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0。



仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相联系的选择列表中才允许分组。



语法

GROUPING ( column_name )



参数

column_name



是 GROUP BY 子句中用于检查 CUBE 或 ROLLUP 空值的列。



返回类型

int



注释

分组用于区分由 CUBE 和 ROLLUP 返回的空值和标准的空值。作为CUBE 或 ROLLUP 操作结果返回的 NULL 是 NULL 的特殊应用。它在结果集内作为列的占位符，意思是"全体"。



示例

下面的示例将 royalty 的数值分组，并聚合 advance 的数值。GROUPING 函数应用于 royalty 列。



USE pubs

SELECT royalty, SUM(advance) 'total advance', 

   GROUPING(royalty) 'grp'

   FROM titles

   GROUP BY royalty WITH ROLLUP



结果集在 royalty 下显示两个空值。第一个 NULL 代表从表中这一列得到的空值组。第二个 NULL 在 ROLLUP 操作所添加的汇总行中。汇总行显示的是所有 royalty 组的 advance 合计数值，并且在 grp 列中用 1 标识。



下面是结果集：



royalty        total advance              grp 

---------      ---------------------    ---

NULL           NULL                     0  

10             57000.0000               0  

12             2275.0000                0  

14             4000.0000                0  

16             7000.0000                0  

24             25125.0000               0  

NULL           95400.0000               1

liangCK 2009-01-01

打赏
举报

聚合函数；当行由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。

仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相关联的选择列表中才允许分组。

Transact-SQL 语法约定

语法

GROUPING ( column_name )

参数
column_name
GROUP BY 子句中的列，用于测试 CUBE 或 ROLLUP 空值。

返回类型
tinyint

备注
分组用于区分由标准空值产生的 CUBE 和 ROLLUP 所返回的空值。作为 CUBE 或 ROLLUP 操作结果返回的 NULL 是 NULL 的特殊应用。它在结果集内作为列的占位符，表示全体。

示例
以下示例将分组 SalesQuota 并聚合 SaleYTD 数量。GROUPING 函数应用于 SalesQuota 列。

复制代码
USE AdventureWorks;
GO
SELECT SalesQuota, SUM(SalesYTD) 'TotalSalesYTD', GROUPING(SalesQuota) AS 'Grouping'
FROM Sales.SalesPerson
GROUP BY SalesQuota WITH ROLLUP;
GO

结果集在 SalesQuota 下面显示两个空值。第一个 NULL 代表从表中的这一列得到的空值组。第二个 NULL 位于 ROLLUP 操作所添加的汇总行之中。汇总行显示所有 SalesQuota 组的 TotalSalesYTD 数量，并以 Grouping 列中的 1 进行指示。

下面是结果集：

复制代码
SalesQuota TotalSalesYTD Grouping
--------- ------------- --------
NULL 1533087.5999 0
250000.00 33461260.59 0
300000.00 9299677.9445 0
NULL 44294026.1344 1

(4 row(s) affected)

liangCK 2009-01-01

打赏
举报

DECLARE @t TABLE(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)

INSERT @t SELECT 'aa','Table','Blue', 124

UNION ALL SELECT 'bb','Table','Red',  -23

UNION ALL SELECT 'bb','Cup'  ,'Green',-23

UNION ALL SELECT 'aa','Chair','Blue', 101

UNION ALL SELECT 'aa','Chair','Red',  -90



--汇总显示

SELECT Groups=CASE 

		WHEN GROUPING(Color)=0 THEN Groups

		WHEN GROUPING(Groups)=1 THEN '总计'

		ELSE '' END,

	Item=CASE 

		WHEN GROUPING(Color)=0 THEN Item

		WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'

		ELSE '' END,

	Color=CASE 

		WHEN GROUPING(Color)=0 THEN Color

		WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'

		ELSE '' END,

	Quantity=SUM(Quantity)

FROM @t

GROUP BY Groups,Item,Color WITH ROLLUP

/*--结果

Groups Item       Color           Quantity    

-------- ---------------- ---------------------- ----------- 

aa     Chair      Blue            101

aa     Chair      Red             -90

                 Chair 小计       11

aa     Table      Blue            124

                 Table 小计       124

       aa 合计                    135

bb     Cup        Green           -23

                  Cup 小计        -23

bb     Table      Red             -23

                 Table 小计       -23

       bb 合计                    -46

总计                              89

--*/