select * 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 ---------- ---------- ---------------------- ---------------------- 2011-01-01 1011 165 15 2012-01-12 1011 225 125 2012-02-12 1011 862 25 小计 1011 1252 165 2012-01-08 1012 145 25 2012-02-14 1012 25 12 小计 1012 170 37 总计 NULL 1422 202 */
--少了一列,补上 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 ROLLUPWITH 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
你可以自由修改,,不要时间就去掉分组。。 小计按照bid统计。总计按照所有统计。
;with maco as (select row_number() over (partition by bid order by getdate()) as num ,* from payment where pid = 3000001)
select min(convert(varchar(10), paytime, 120)) as paytime , case when num is null and bid is null then '总计' when num is null then '小计' else ltrim(bid) end as bid , sum(pay) as pay ,sum(pay_count) as pay_count from maco group by bid ,num with rollup /* paytime bid pay pay_count ---------- ---------- ---------------------- ---------------------- 2012-01-12 1011 225 125 2011-01-01 1011 165 15 2012-02-12 1011 862 25 2011-01-01 小计 1252 165 2012-02-14 1012 25 12 2012-01-08 1012 145 25 2012-01-08 小计 170 37 2011-01-01 总计 1422 202 */
--字段写错了 ;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_count from maco group by bid ,num with rollup /* paytime bid pay pay_count ---------- ---------- ---------------------- ---------------------- 2012-01-12 1011 225 125 2011-01-01 1011 165 15 2012-02-12 1011 862 25 小计 1011 1252 165 2012-02-14 1012 25 12 2012-01-08 1012 145 25 小计 1012 170 37 总计 NULL 1422 202 */
再次进行数据插入,如何在原有的结果上增加一列交易笔数, 统计每天的交易数量,并根据日期进行分组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
还有就是显示user表里的name字段,以及payment里的pid
select case when GROUPING(CONVERT(nvarchar(101),paytime,23)) =1 then '合计' else CONVERT(nvarchar(101),paytime,23)end as dateid, sum(pay) as pay ,sum(pay_count) as pay_count,sum(p.pay-p.pay_count) as cash,count(*)[count] from payment p inner join users u on p.pid=u.pid where p.pid='3000001' group by CONVERT(nvarchar(101),paytime,23) with rollupdateid pay pay_count cash count 2011-01-01 330 30 300 2 2012-01-08 290 50 240 2 2012-01-12 450 250 200 2 2012-02-12 1724 50 1674 2 2012-02-14 50 24 26 2 合计 2844 404 2440 10
select *
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
---------- ---------- ---------------------- ----------------------
2011-01-01 1011 165 15
2012-01-12 1011 225 125
2012-02-12 1011 862 25
小计 1011 1252 165
2012-01-08 1012 145 25
2012-02-14 1012 25 12
小计 1012 170 37
总计 NULL 1422 202
*/
--少了一列,补上
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
*/
(
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
你可以自由修改,,不要时间就去掉分组。。
小计按照bid统计。总计按照所有统计。
;with maco as (select
row_number() over (partition by bid order by getdate()) as num ,*
from payment where pid = 3000001)
select min(convert(varchar(10), paytime, 120)) as paytime ,
case when num is null and bid is null then '总计'
when num is null then '小计' else ltrim(bid) end as bid ,
sum(pay) as pay ,sum(pay_count) as pay_count
from maco group by bid ,num with rollup
/*
paytime bid pay pay_count
---------- ---------- ---------------------- ----------------------
2012-01-12 1011 225 125
2011-01-01 1011 165 15
2012-02-12 1011 862 25
2011-01-01 小计 1252 165
2012-02-14 1012 25 12
2012-01-08 1012 145 25
2012-01-08 小计 170 37
2011-01-01 总计 1422 202
*/
--字段写错了
;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_count
from maco group by bid ,num with rollup
/*
paytime bid pay pay_count
---------- ---------- ---------------------- ----------------------
2012-01-12 1011 225 125
2011-01-01 1011 165 15
2012-02-12 1011 862 25
小计 1011 1252 165
2012-02-14 1012 25 12
2012-01-08 1012 145 25
小计 1012 170 37
总计 NULL 1422 202
*/
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
select case when GROUPING(CONVERT(nvarchar(101),paytime,23)) =1 then '合计' else CONVERT(nvarchar(101),paytime,23)end as dateid, sum(pay) as pay ,sum(pay_count) as pay_count,sum(p.pay-p.pay_count) as cash,count(*)[count]
from payment p inner join users u on p.pid=u.pid
where p.pid='3000001'
group by CONVERT(nvarchar(101),paytime,23) with rollupdateid pay pay_count cash count
2011-01-01 330 30 300 2
2012-01-08 290 50 240 2
2012-01-12 450 250 200 2
2012-02-12 1724 50 1674 2
2012-02-14 50 24 26 2
合计 2844 404 2440 10