能否在SQL中直接实现小计与合计

Question

CREATE TABLE [dbo].[TEST](
[MB001] [nvarchar](50) COLLATE Chinese_PRC_BIN NOT NULL,
[MB006] [nchar](10) COLLATE Chinese_PRC_BIN NOT NULL,
[MC002] [nchar](6) COLLATE Chinese_PRC_BIN NOT NULL,
[MB015] [nchar](4) COLLATE Chinese_PRC_BIN NOT NULL,
[MC007] [float] NOT NULL,
[MB014] [float] NOT NULL,
[SUM] [float] NOT NULL,
 CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED 
(
[MB001] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO TEST   VALUES ('28102-00140-000', '038','1072','Kg',130, 0.112,14.56 )
INSERT INTO TEST   VALUES ('28102-00141-000', '038','1072','Kg', 115, 0.123,14.145 )
INSERT INTO TEST   VALUES ('28102-00401-000', '038','1072','Kg', 1329, 0.11,146.19 )
INSERT INTO TEST   VALUES ('28102-00465-000', '038','1072','g' ,19, 0.013880,0.26372 )
INSERT INTO TEST   VALUES ('28102-00496-000', '038','1072','g' , 28 ,0, 0)
INSERT INTO TEST   VALUES ('28102-00001-000', '038','1072A','Kg',7130,0.081540,581.3802)
INSERT INTO TEST   VALUES ('28102-00002-000', '038','1072A','Kg', 50,0.013770,0.6885 )
INSERT INTO TEST   VALUES ('28102-00454-000', '038','1072A','g' , 1000,50,65000 )
INSERT INTO TEST   VALUES ('28102-00455-000', '038','1072A','g' , 2456,74,181744 )
固定显示：SELECT *
FROM TEST
ORDER BY MB006,MC002,MB015,MB001报表格式：根据MB006排序（第一个条件排序，因为MB006有许多的值，如001，002，。。），第二根据MC002排序（与MB006一样，有多个仓库：101，102，103。。），然后根据MB015排序（只有KG，g两个值），再根据MB001排序。
能否在数据库中实现如下功能：
 根据排序序条件：
第一点 计算:MB006相同，MC002(仓库)也是相同，MB015为KG或g的，分别计算他们的SUM值，并且g的总值要除以1000（换算为KG），这个值要插入到相关的数据后面，
比如：MB006 为038 MC002为1072 MB015 为KG 得出KG的值插入到第4行，另加多一个字段值最好 （小计）
第二点：计算出MB006相同的，SUM的总和，单位为KG。另加多一个字段值最好。 （MB006 相同的合计）
第三点：因为MB006，MC002的值为多个，所以不能直接等于XX，如：038，而是只能判断是否相等。能否直接通过SQL实现。

Answer 1

declare @tb table(nostr varchar(5),col2 varchar(5),col3 varchar(5), col4 varchar(5))
insert @tb
select '1','1','1','a1'union all
select '1','1','2','a2'union all
select '1','1','3','a3'union all 
select '1','1','4','a4'union all 
select '1','2','1','a5'union all 
select '1','2','2','a6'union all
select '1','2','4','a7'union all
select '2','1','1','b8'union all
select '2','1','2','b9'union all
select '2','1','3','b10'union all
select '2','2','1','b11'union all
select '2','2','2','b12'union all
select '2','2','4','b13'--更经典的。
--with rollup实际起一个汇总作用。
select * from @tb
union all
select isnull(NoStr,'合计'),'小计',null,ltrim(count(1)) from @tb
group by NoStr with rollup/*
nostr col2  col3  col4         
----- ----- ----- ------------ 
1     1     1     a1
1     1     2     a2
1     1     3     a3
1     1     4     a4
1     2     1     a5
1     2     2     a6
1     2     4     a7
2     1     1     b8
2     1     2     b9
2     1     3     b10
2     2     1     b11
2     2     2     b12
2     2     4     b13
1     小计    NULL  7
2     小计    NULL  6
合计    小计    NULL  13（所影响的行数为 16 行）
*/

Answer 2

首先创建测试表、添加数据。 
create table #t(a int,b int,c int,d int,e int) 
insert into #t values(1,2,3,4,5) 
insert into #t values(1,2,3,4,6) 
insert into #t values(1,2,3,4,7) 
insert into #t values(1,2,3,4,8) 
insert into #t values(1,3,3,4,5) 
insert into #t values(1,3,3,4,6) 
insert into #t values(1,3,3,4,8) 
insert into #t values(1,3,3,4,7) insert into #t values(2,2,2,4,5) 
insert into #t values(2,2,3,4,6) 
insert into #t values(2,2,4,4,7) 
insert into #t values(2,2,5,4,8) 
insert into #t values(2,3,6,4,5) 
insert into #t values(2,3,3,4,6) 
insert into #t values(2,3,3,4,8) 
insert into #t values(2,3,3,4,7)
情况一：只有一个分类汇总列时，只需要一个合计。只需要增加with rollup即可。 
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
sum(b),sum(c),sum(d),sum(e) from #t group by a with rollup 
情况二:有多个分类汇总列，只需要一个合计.增加rollup之后，需要增加判断。 
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
    b, 
sum(c),sum(d),sum(e) from #t 
group by a,b with rollup 
having grouping(b)=0 or grouping(a)=1select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
    b, 
    c, 
sum(d),sum(e) from #t 
group by a,b,c with rollup 
having grouping(c)=0 or grouping(a)=1 
情况三：有多个分类汇总列，需要全部的小计和合计。 
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
    case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b, 
    case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c, 
sum(d),sum(e) from #t 
group by a,b,c with rollup 
另外一种显示小计的方式 
select case when grouping(a)=1 then '合计' 
    when grouping(b)=1 then cast(a as varchar)+'小计' 
    else cast(a as varchar) end a, 
    case when grouping(b)=0 and grouping(c)=1 
    then cast(b as varchar)+'小计' else cast(b as varchar) end b, 
    case when grouping(c)=1 and grouping(b)=0 
    then '' else cast(c as varchar) end c, 
sum(d),sum(e) from #t 
group by a,b,c with rollup 
情况四：有多个分类汇总列，需要部分的小计和合计 
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
    b, 
    case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c, 
sum(d),sum(e) from #t 
group by a,b,c with rollup 
having grouping(a)=1 or grouping(b)=0 
select case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
    case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b, 
    c, 
sum(d),sum(e) from #t 
group by a,b,c with rollup 
having grouping(a)=1 or grouping(b)=1 or grouping(c)=

Answer 3

CREATE TABLE test ( 
[MB001] [nvarchar](50) COLLATE Chinese_PRC_BIN NOT NULL, 
[MB006] [nchar](10) COLLATE Chinese_PRC_BIN NOT NULL, 
[MC002] [nchar](6) COLLATE Chinese_PRC_BIN NOT NULL, 
[MB015] [nchar](4) COLLATE Chinese_PRC_BIN NOT NULL, 
[MC007] [float] NOT NULL, 
[MB014] [float] NOT NULL, 
[SUM] [float] NOT NULL, 
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED 
( 
[MB001] ASC 
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] 
) ON [PRIMARY] 
INSERT INTO TEST  VALUES ('28102-00140-000', '038','1072','Kg',130, 0.112,14.56 ) 
INSERT INTO TEST  VALUES ('28102-00141-000', '038','1072','Kg', 115, 0.123,14.145 ) 
INSERT INTO TEST  VALUES ('28102-00401-000', '038','1072','Kg', 1329, 0.11,146.19 ) 
INSERT INTO TEST  VALUES ('28102-00465-000', '038','1072','g' ,19, 0.013880,0.26372 ) 
INSERT INTO TEST  VALUES ('28102-00496-000', '038','1072','g' , 28 ,0, 0) 
INSERT INTO TEST  VALUES ('28102-00001-000', '038','1072A','Kg',7130,0.081540,581.3802) 
INSERT INTO TEST  VALUES ('28102-00002-000', '038','1072A','Kg', 50,0.013770,0.6885 ) 
INSERT INTO TEST  VALUES ('28102-00454-000', '038','1072A','g' , 1000,50,65000 ) 
INSERT INTO TEST  VALUES ('28102-00455-000', '038','1072A','g' , 2456,74,181744 )go
select * ,小计= sum([sum]) over(partition by MC002),合计=sum([sum]) over() from test
go
drop  table test
go