if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[pactNo] varchar(5),[amount] int,[ReceivablesDate] datetime)
insert [tb] select 1,'001',100,'2008-11-14'
union all select 2,'001',100,'2008-12-14'
union all select 3,'001',100,'2009-01-14'
union all select 4,'001',100,'2009-02-14'
union all select 5,'001',100,'2009-03-14'
union all select 6,'001',100,'2008-12-16'
union all select 7,'001',100,'2009-01-16'
union all select 8,'001',100,'2009-02-16'
union all select 9,'001',100,'2009-03-16' ---查询---
select pactNo,convert(varchar(7),ReceivablesDate,120) as ReceivablesDate,sum(amount) as amount into # from tb group by pactNo,convert(varchar(7),ReceivablesDate,120)select
isnull(日期,'合同累计收款') as 日期,
合同编号,
本月收款,
累计收款
from
(
select
日期=ReceivablesDate,
合同编号=pactNo,
本月收款=ltrim(amount),
累计收款=ltrim((select sum(amount) from # where pactNo=t.pactNo and left(ReceivablesDate,4)=left(t.ReceivablesDate,4) and ReceivablesDate<=t.ReceivablesDate))
from # t
union all
select ltrim(year(ReceivablesDate))+'总计','','',sum(amount) from tb group by ltrim(year(ReceivablesDate))+'总计' with rollup
) t
order by isnull(日期,'合同累计收款') /**
日期 合同编号 本月收款 累计收款
---------------- ----- ------------ -----------
2008-11 001 100 100
2008-12 001 200 300
2008总计 300
2009-01 001 200 200
2009-02 001 200 400
2009-03 001 200 600
2009总计 600
合同累计收款 900(所影响的行数为 8 行)**/
go
create table [tb]([ID] int,[pactNo] varchar(5),[amount] int,[ReceivablesDate] datetime)
insert [tb] select 1,'001',100,'2008-11-14'
union all select 2,'001',100,'2008-12-14'
union all select 3,'001',100,'2009-01-14'
union all select 4,'001',100,'2009-02-14'
union all select 5,'001',100,'2009-03-14'
union all select 6,'001',100,'2008-12-16'
union all select 7,'001',100,'2009-01-16'
union all select 8,'001',100,'2009-02-16'
union all select 9,'001',100,'2009-03-16' ---查询---
select pactNo,convert(varchar(7),ReceivablesDate,120) as ReceivablesDate,sum(amount) as amount into # from tb group by pactNo,convert(varchar(7),ReceivablesDate,120)select
isnull(日期,'合同累计收款') as 日期,
合同编号,
本月收款,
累计收款
from
(
select
日期=ReceivablesDate,
合同编号=pactNo,
本月收款=ltrim(amount),
累计收款=ltrim((select sum(amount) from # where pactNo=t.pactNo and left(ReceivablesDate,4)=left(t.ReceivablesDate,4) and ReceivablesDate<=t.ReceivablesDate))
from # t
union all
select ltrim(year(ReceivablesDate))+'总计','','',sum(amount) from tb group by ltrim(year(ReceivablesDate))+'总计' with rollup
) t
order by isnull(日期,'合同累计收款') /**
日期 合同编号 本月收款 累计收款
---------------- ----- ------------ -----------
2008-11 001 100 100
2008-12 001 200 300
2008总计 300
2009-01 001 200 200
2009-02 001 200 400
2009-03 001 200 600
2009总计 600
合同累计收款 900(所影响的行数为 8 行)**/
with
wang1 as (select distinct 日期=convert(varchar(7),ReceivablesDate,120),pactNo,
本月收款=(select sum(amount) from tb where pactno=t.pactno and convert(varchar(7),ReceivablesDate,120)=convert(varchar(7),t.ReceivablesDate,120))
from tb t),
wang2 as (select
日期=convert(varchar(7),ReceivablesDate,120),
累计收款=sum(amount)
from tb t
group by convert(varchar(7),ReceivablesDate,120)
union all
select
日期=convert(varchar(4),ReceivablesDate,120)+'总计',
累计收款=sum(amount)
from tb t
group by convert(varchar(4),ReceivablesDate,120)
)
select 日期=isnull(wang1.日期,wang2.日期),pactno,本月收款,累计收款
from wang1 right join wang2 on wang1.日期=wang2.日期
order by isnull(wang1.日期,wang2.日期)日期 pactno 本月收款 累计收款
2008-11 001 100 100
2008-12 001 200 200
2008总 NULL NULL 300
2009-01 001 200 200
2009-02 001 200 200
2009-03 001 200 200
2009总 NULL NULL 600