Tsql统计之二（实例详解grouping() case设置合计字段）

最新推荐文章于 2022-09-01 21:15:28 发布

weixin_30877493

最新推荐文章于 2022-09-01 21:15:28 发布

阅读量164

点赞数

原文链接：http://www.cnblogs.com/gaiyang/archive/2011/04/17/2018833.html

版权

grouping()函数：grouping(字段)=1则对字段进行了合计，grouping(字段）=0则此行没有对字段合计，

根据这个特性我们可以控件要显示的统计列表，例子如下：

View Code

DROP TABLE [ product ]
CREATE TABLE [ product ] ( [ proid ] [ varchar ] ( 5 ) NULL , [ in_date ] [ varchar ] ( 10 ) NULL , [ qty ] [ int ] NULL )

INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 1 ' , ' 2004-1-2 ' , 10 )
INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 2 ' , ' 2004-1-3 ' , 13 )
INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 3 ' , ' 2004-1-4 ' , 10 )
INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 4 ' , ' 2004-1-4 ' , 13 )
INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 5 ' , ' 2004-1-5 ' , 14 )
INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 6 ' , ' 2004-1-6 ' , 15 )
INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 7 ' , ' 2004-1-7 ' , 63 )
INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 8 ' , ' 2004-1-8 ' , 43 )
INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 3 ' , ' 2004-1-4 ' , 10 )
INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 4 ' , ' 2004-1-4 ' , 13 )
INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 5 ' , ' 2004-1-5 ' , 14 )
INSERT [ product ] ( [ proid ] , [ in_date ] , [ qty ] ) VALUES ( ' 3 ' , ' 2004-1-5 ' , 10

思路分析：先写出最原始的查询，然后根据需要判断grouping(字段）和用case 显示字段值

View Code

select
case grouping (proid) when 1 then ' 合计 ' -- 在这里处理字符显示　
     when 0 then case grouping (in_date)
                 when 1 then proid + ' 小计 '
                 when 0 then proid
     end
end
proid,in_date, sum (qty) ' 数量 ' , grouping (proid) as ' gpProid ' ,
grouping (in_date) as ' gpIn_date '
from product
group by proid,in_date with rollup

having grouping (proid) + grouping (in_date) = 0 -- 显示常规的sum值,没有合计
or grouping (proid) + grouping (in_date) = 1
or grouping (proid) + grouping (in_date) = 2 　　 -- 按proid,in_date总计
order by proid   -- 只有按proid,in_date的sum(qty)合计

转载于:https://www.cnblogs.com/gaiyang/archive/2011/04/17/2018833.html