合计问题

[golden24kcn]

Payid stdName cash pay pay_count
3000001 艾鹤 0.00 125.00 125.00
3000002 王艳辉 0.00 139.00 139.00
3000003 白阿乐 0.00 175.00 175.00
3000004 刘珊 0.00 175.00 175.00

求结果为
Payid stdName cash pay pay_count
3000001 艾鹤 0.00 125.00 125.00
3000002 王艳辉 0.00 139.00 139.00
3000003 白阿乐 0.00 175.00 175.00
3000004 刘珊 0.00 175.00 175.00
合计 0.00 xxxx.xx xxxx.xx

我的SQL
SELECT Payid = CASE grouping (Payid)WHEN 0 THEN Payid ELSE '合计'END, stdname, sum(cash),sum(pay),sum(pay_count) from table1 a group by payid, stdname with ROLLUP

可是得出的结果是
Payid stdname 1 2
3000001 艾鹤 0.00 125.00 125.00
3000001 {null} 0.00 125.00 125.00
3000002 王艳辉 0.00 139.00 139.00
3000002 {null} 0.00 139.00 139.00
3000003 白阿乐 0.00 175.00 175.00
3000003 {null} 0.00 175.00 175.00
3000004 刘珊 0.00 175.00 175.00
3000004 {null} 0.00 175.00 175.00
合计 0.00 xxxx.xx xxxxx.xx

多出了一个null行，合计金额也多了一倍

[水族杰纶]

with cube

[chuifengde]

SELECT * FROM T
UNION
SELECT '合计','',sum(cash),sum(pay),sum(pay_count)from T

[wzy_love_sly]

create table tb(Payid int, stdName varchar(50), cash decimal(18,2), pay decimal(18,2), pay_count decimal(18,2))
insert into tb select 3000001,'艾鹤',0.00,125.00,125.00
insert into tb select 3000001,'王艳辉',0.00,139.00,139.00
insert into tb select 3000001,'白阿乐',0.00,125.00,175.00
insert into tb select 3000001,'刘珊',0.00,175.00,175.00
--1
SELECT isnull(ltrim(payid),'合计') as payid,isnull(stdname,'') as stdname,
sum(cash) as cash,sum(pay) as pay,sum(pay_count) as pay_count
from tb  group by payid, stdname 
with ROLLUP
having grouping (Payid)+grouping (stdname) in(0,2)
--2
select ltrim(payid) as payid,stdname,cash,pay,pay_count from tb
union all
select '合计','',sum(cash) as cash,sum(pay) as pay,sum(pay_count) as pay_count from tb

payid stdname cash pay pay_count
3000001 艾鹤 0.00 125.00 125.00
3000001 白阿乐 0.00 125.00 175.00
3000001 刘珊 0.00 175.00 175.00
3000001 王艳辉 0.00 139.00 139.00
合计 0.00 564.00 614.00

[一品梅]

union

[一品梅]

union+统计函数

[regithanhu]

create table test
(
Payid varchar(10),
stdName varchar(10),
cash    money,
pay     money,
pay_count money
)

insert into  test
select'3000001', '艾鹤' ,0.00, 125.00, 125.00
union all 
select '3000002', '王艳辉', 0.00, 139.00 ,139.00 
union all
select '3000003', '白阿乐', 0.00, 175.00,175.00 
union all
select '3000004', '刘珊', 0.00, 175.00, 175.00 
--方法一
select case grouping(Payid)
       when 0 then Payid
       when 1 then '总计'
       end Payid,
      stdName,
      sum(cash) cash,
      sum(pay)  pay,
      sum(pay_count) pay_count
from test
group by Payid,stdName
  with rollup
having grouping(Payid)+grouping(stdName)in (0,2)

--方法二
select * from test
union all
select '总计', '', sum(cash) cash, sum(pay)pay, sum(pay_count) pay_count
  from test 

drop table test 
如果数据量大的话建议你使用方法一因为一是系统执行的时候就已经分类汇总了，效率高点
以上个人意见