create table users
(
  pid varchar(10),
  name  varchar(50)
)


create table payment
(
    payTime datetime,
    pid varchar(10),
    bid varchar(10),
    pay   float,
    pay_count   float,
)

insert into  users
select'3000001', '艾鹤' 
union all 
select '3000002', '王艳辉'
union all
select '3000003', '刘珊' 


insert into  payment
select'2012-01-12','3000001', '1011',225.00, 125.00
union all 
select'2012-01-08','3000001', '1012' ,145.00, 25.00
union all 
select'2011-01-01','3000001', '1011' ,165.00, 15.00
union all 
select '2012-01-15','3000002', '1013',78.00, 5.00
union all
select '2012-02-05','3000003', '1014' ,580.00, 25.00
union all
select '2012-02-12','3000001', '1011' ,862.00, 25.00
union all
select '2012-02-14','3000001', '1012' ,25.00, 12.00


查询pid为 3000001  结果为： 

paytime     bid,   pay,  pay_count  cash
2012-01-12  1011    225   125        100 
2012-01-01  1011    165   15         150
2012-02-12  1011    862   25         837   
小计          1011    1252  165        1087

2012-01-08  1012    145   25         120
2012-02-14  1012    25    12         13
小计          1011    170   37         133

总计                   1422  202        1220

--少了一列，补上
select  * ,
        pay - pay_count as cash
from    ( select    convert(varchar(10), paytime, 120) as paytime ,
                    bid ,
                    pay ,
                    pay_count
          from      payment
          where     pid = 3000001
          union all
          select    *
          from      ( select    '小计' as c1 ,
                                bid ,
                                sum(pay) as c3 ,
                                sum(pay_count) c4
                      from      payment
                      where     pid = 3000001
                      group by  bid
                      union all
                      select    '总计' as c1 ,
                                null as c2 ,
                                sum(pay) as c3 ,
                                sum(pay_count) c4
                      from      payment
                      where     pid = 3000001
                    ) a
        ) b
order by isnull(bid, 9999) ,paytime

/*
paytime    bid        pay                    pay_count              cash
---------- ---------- ---------------------- ---------------------- ----------------------
2011-01-01 1011       165                    15                     150
2012-01-12 1011       225                    125                    100
2012-02-12 1011       862                    25                     837
小计         1011       1252                   165                    1087
2012-01-08 1012       145                    25                     120
2012-02-14 1012       25                     12                     13
小计         1012       170                    37                     133
总计         NULL       1422                   202                    1220
*/

------解决方案--------------------
用的 WITH ROLLUP


SQL code



WITH E AS 
(
    SELECT u.[pid],u.[name],p.bid,p.pay,p.pay_count,p.payTime
    FROM [master].[dbo].[users] u join [master].dbo.payment p on u.pid=p.pid
    where u.pid = '3000001'
)
SELECT CASE when payTime IS NULL and bid IS NULL then  '总计' when payTime IS NULL then '小计' ELSE CONVERT(NVARCHAR(10),payTime,120) end as payTime,
    bid,sum(pay) as pay,sum(pay_count) as pay_count,sum(pay-pay_count) as cash 
FROM E
GROUP BY bid,payTime
WITH ROLLUP

------解决方案--------------------

SQL code

--字段写错了
;with maco as (select