求合计总额的存储过程．
CREATE PROCEDURE b_test @YCLRKH varchar(10),@GCRCLMLH varchar(15),@CKDM int,@FPHM varchar(10)
AS
begin
select a.YCLRKH,a.SL,a.ZZL,a.JE,sum(a.JE)as CJE, a.GCRCLMLH,a.CKDM,b.GCMC,b.FPHM as FPHM ,b.FPJE, c.LJMC,c.TZCC,c.CLPH,c.JH from BCPRKTZB a left join JGCHSJB b on a.BCPSLDID=b.BCPSLDID left join CLB c on a.RKXH=C.RKXH
where (a.YCLRKH like '%' + @YCLRKH+ '%' or @YCLRKH='')
and (a.GCRCLMLH like '%' + @GCRCLMLH+ '%' or @GCRCLMLH='')
and (b.FPHM like '%' + @FPHM+ '%' or @FPHM='')
and(a.CKDM = +convert(varchar(10),@CKDM) or @CKDM<0 )
end
GO
有三张表
表A
RKXH YCLRKH SL ZZL JE GCRCLMLH BCPSLDID
1 A01 5 2 2 AA 1
2 A02 2 5 3 BB 2
3 A03 1 2 5 CC 2
表B
GCMC FPHM FPJE
A1 AA1 1
B1 BB1 1
C1 CC1 1
表C
RKXH LJMC
1 TA1
2 TB2
3 TC3
希望得到的结果是：
RKXH YCLRKH SL ZZL JE GCRCLMLH GCMC FPHM FPJE LJMC
1 A01 5 2 2 AA A1 AA1 1 TA1
2 A02 2 5 3 BB B1 BB1 1 TB2
3 A03 1 2 5 CC C1 CC1 1 TC3
合计： 9 10
主要是最后一行的合计，我不知如何写，现在如果我像上面红色标的那样．得到的合计是列，而我要的合计是增加一行．不如何实现．
[解决办法]
CREATE PROCEDURE b_test @YCLRKH varchar(10),@GCRCLMLH varchar(15),@CKDM int,@FPHM varchar(10) ASbeginselect ltrim(a.YCLRKH)YCLRKH,a.SL,a.ZZL,a.JE,sum(a.JE)as CJE, a.GCRCLMLH,a.CKDM,b.GCMC,b.FPHM as FPHM ,b.FPJE, c.LJMC,c.TZCC,c.CLPH,c.JH from BCPRKTZB a left join JGCHSJB b on a.BCPSLDID=b.BCPSLDID left join CLB c on a.RKXH=C.RKXHwhere (a.YCLRKH like '%' + @YCLRKH+ '%' or @YCLRKH='')and (a.GCRCLMLH like '%' + @GCRCLMLH+ '%' or @GCRCLMLH='')and (b.FPHM like '%' + @FPHM+ '%' or @FPHM='')and(a.CKDM = +convert(varchar(10),@CKDM) or @CKDM<0 )union allselect '合计：','','',sum(a.JE), sum(a.GCRCLMLH),'','','' ,'', '','','','' from BCPRKTZB a left join JGCHSJB b on a.BCPSLDID=b.BCPSLDID left join CLB c on a.RKXH=C.RKXHwhere (a.YCLRKH like '%' + @YCLRKH+ '%' or @YCLRKH='')and (a.GCRCLMLH like '%' + @GCRCLMLH+ '%' or @GCRCLMLH='')and (b.FPHM like '%' + @FPHM+ '%' or @FPHM='')and(a.CKDM = +convert(varchar(10),@CKDM) or @CKDM<0 )endGO
