用rollup declare @sql varchar(8000) set @sql = 'select vProvince 省份,sum(iAmount) 全年合计' select @sql = @sql + ',sum(case dMonthDate when '''+cast(dMonthDate as varchar)+''' then iAmount else 0 end) ['+cast(dMonthDate as varchar)+'月]' from (select distinct dMonthDate from viewRecord) as a select @sql = @sql+' from viewRecord group by vProvince with rollup'exec(@sql) go
use grouping()declare @sql varchar(8000) set @sql = 'select vProvince as 省份,sum(iAmount) 全年合计' select @sql = @sql + ',sum(case dMonthDate when '''+cast(dMonthDate as varchar)+''' then iAmount else 0 end) ['+cast(dMonthDate as varchar)+'月]' from (select distinct dMonthDate from viewRecord) as a select @sql = @sql+' grouping(vProvice) ''addcolumn'' from viewRecord group by vProvince WITH ROLLUP'addcolumn=1 就是合计。 or try: declare @sql varchar(8000) set @sql = 'select vProvince as 省份,sum(iAmount) 全年合计' select @sql = @sql + ',sum(case dMonthDate when '''+cast(dMonthDate as varchar)+''' then iAmount else 0 end) ['+cast(dMonthDate as varchar)+'月]' from (select distinct dMonthDate from viewRecord) as a select @sql = @sql+' grouping(vProvice) ''addcolumn'' from viewRecord group by vProvince WITH cube'
还没通过,提示说:Incorrect syntax near 'GROUPING'.declare @sql varchar(8000) set @sql = 'select vProvince 省份,sum(iAmount) 全年合计' select @sql = @sql + ',sum(case dMonthDate when '''+cast(dMonthDate as varchar)+''' then iAmount else 0 end) ['+cast(dMonthDate as varchar)+'月]' from (select distinct dMonthDate from viewRecord) as a select @sql = @sql+' grouping(vProvince) ''addcolumn'' from viewRecord group by vProvince with rollup'exec(@sql) go
加逗号后通过,但只是增加一列,并没有把 合计的字段加上,如下所示:省份 全年合计 01月 02月 03月12月 /*/合计/*/ 65 10 14 8 12 北京 25 5 5 2 3 上海 20 3 4 5 3 广东 20 2 5 1 6declare @sql varchar(8000) set @sql = 'select vProvince 省份,sum(iAmount) 全年合计' select @sql = @sql + ',sum(case dMonthDate when '''+cast(dMonthDate as varchar)+''' then iAmount else 0 end) ['+cast(dMonthDate as varchar)+'月]' from (select distinct dMonthDate from viewRecord) as a select @sql = @sql+' /*/,/*/grouping(vProvince) ''addcolumn'' from viewRecord group by vProvince with rollup'exec(@sql) go
下面的示例将 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 -----------------------------------------------------TRY:
declare @sql varchar(8000)
set @sql = 'select vProvince 省份,sum(iAmount) 全年合计'
select @sql = @sql + ',sum(case dMonthDate when '''+cast(dMonthDate as varchar)+''' then iAmount else 0 end) ['+cast(dMonthDate as varchar)+'月]'
from (select distinct dMonthDate from viewRecord) as a
select @sql = @sql+' from viewRecord group by vProvince WITH ROLLUP'exec(@sql)
go
NULL 65 10 14 8 12
北京 25 5 5 2 3
上海 20 3 4 5 3
广东 20 2 5 1 6
declare @sql varchar(8000)
set @sql = 'select vProvince 省份,sum(iAmount) 全年合计'
select @sql = @sql + ',sum(case dMonthDate when '''+cast(dMonthDate as varchar)+''' then iAmount else 0 end) ['+cast(dMonthDate as varchar)+'月]'
from (select distinct dMonthDate from viewRecord) as a
select @sql = @sql+' from viewRecord group by vProvince with rollup'exec(@sql)
go
set @sql = 'select vProvince as 省份,sum(iAmount) 全年合计'
select @sql = @sql + ',sum(case dMonthDate when '''+cast(dMonthDate as varchar)+''' then iAmount else 0 end) ['+cast(dMonthDate as varchar)+'月]'
from (select distinct dMonthDate from viewRecord) as a
select @sql = @sql+' grouping(vProvice) ''addcolumn'' from viewRecord group by vProvince WITH ROLLUP'addcolumn=1 就是合计。
or try:
declare @sql varchar(8000)
set @sql = 'select vProvince as 省份,sum(iAmount) 全年合计'
select @sql = @sql + ',sum(case dMonthDate when '''+cast(dMonthDate as varchar)+''' then iAmount else 0 end) ['+cast(dMonthDate as varchar)+'月]'
from (select distinct dMonthDate from viewRecord) as a
select @sql = @sql+' grouping(vProvice) ''addcolumn'' from viewRecord group by vProvince WITH cube'
set @sql = 'select vProvince 省份,sum(iAmount) 全年合计'
select @sql = @sql + ',sum(case dMonthDate when '''+cast(dMonthDate as varchar)+''' then iAmount else 0 end) ['+cast(dMonthDate as varchar)+'月]'
from (select distinct dMonthDate from viewRecord) as a
select @sql = @sql+' grouping(vProvince) ''addcolumn'' from viewRecord group by vProvince with rollup'exec(@sql)
go
合计的字段加上,如下所示:省份 全年合计 01月 02月 03月12月
/*/合计/*/ 65 10 14 8 12
北京 25 5 5 2 3
上海 20 3 4 5 3
广东 20 2 5 1 6declare @sql varchar(8000)
set @sql = 'select vProvince 省份,sum(iAmount) 全年合计'
select @sql = @sql + ',sum(case dMonthDate when '''+cast(dMonthDate as varchar)+''' then iAmount else 0 end) ['+cast(dMonthDate as varchar)+'月]'
from (select distinct dMonthDate from viewRecord) as a
select @sql = @sql+' /*/,/*/grouping(vProvince) ''addcolumn'' from viewRecord group by vProvince with rollup'exec(@sql)
go