- SQL code
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 usersselect'3000001', '艾鹤' union all select '3000002', '王艳辉'union allselect '3000003', '刘珊' insert into paymentselect'2012-01-12','3000001', '1011',225.00, 125.00union all select'2012-01-08','3000001', '1012' ,145.00, 25.00union all select'2011-01-01','3000001', '1011' ,165.00, 15.00union all select '2012-01-15','3000002', '1013',78.00, 5.00union allselect '2012-02-05','3000003', '1014' ,580.00, 25.00union allselect '2012-02-12','3000001', '1011' ,862.00, 25.00union allselect '2012-02-14','3000001', '1012' ,25.00, 12.00查询pid为 3000001 结果为： paytime bid, pay, pay_count cash2012-01-12 1011 225 125 100 2012-01-01 1011 165 15 1502012-02-12 1011 862 25 837 小计 1011 1252 165 10872012-01-08 1012 145 25 1202012-02-14 1012 25 12 13小计 1011 170 37 133总计 1422 202 1220
注：cash 是 pay -count 根据pid进行查询，查询结果根据 bid 分组 如上，1011,1012 分别进行小计，总计则是对所有数据进行累加
------解决方案--------------------
- SQL code
--少了一列，补上select * , pay - pay_count as cashfrom ( 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 ) border by isnull(bid, 9999) ,paytime/*paytime bid pay pay_count cash---------- ---------- ---------------------- ---------------------- ----------------------2011-01-01 1011 165 15 1502012-01-12 1011 225 125 1002012-02-12 1011 862 25 837小计 1011 1252 165 10872012-01-08 1012 145 25 1202012-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 EGROUP BY bid,payTimeWITH ROLLUP
------解决方案--------------------
- SQL code
--字段写错了;with maco as (select row_number() over (partition by bid order by getdate()) as num ,* from payment where pid = 3000001) select case when num is null and bid is null then '总计' when num is null then '小计' else min(convert(varchar(10), paytime, 120)) end as paytime , bid,sum(pay) as pay ,sum(pay_count) as pay_countfrom maco group by bid ,num with rollup/*paytime bid pay pay_count---------- ---------- ---------------------- ----------------------2012-01-12 1011 225 1252011-01-01 1011 165 152012-02-12 1011 862 25小计 1011 1252 1652012-02-14 1012 25 122012-01-08 1012 145 25小计 1012 170 37总计 NULL 1422 202*/