create table S(PID varchar(30) ,pname nvarchar(10),amt decimal(18,1),paid decimal(18,1),account decimal(18,1))

insert into S  values ('201201','001',6000,6500,500)
insert into S  values ('201201','002',5000,7000,2000)
insert into S  values ('201201','003',600,600,0)
insert into S  values ('201201','004',600,550,-50)
insert into S  values ('201201','005',5000,6000,1000)
insert into S  values ('201202','002',100,650,550)
insert into S  values ('201202','004',6000,16500,10500)
insert into S  values ('201203','001',6000,6500,500)
insert into S  values ('201203','002',5000,7000,2000)
insert into S  values ('201203','005',700,100,-600)
insert into S  values ('201203','006',8000,10500,2500)

--PID 发生期间 ,PNAME 供应商 ，amt 采购金额，paid 付款金额，account 当月余额（即当月paid-amt）

create table S(PID varchar(30) ,pname nvarchar(10),amt decimal(18,1),paid decimal(18,1),account decimal(18,1))
insert into S  values ('201201','001',6000,6500,500)
insert into S  values ('201201','002',5000,7000,2000)
insert into S  values ('201201','003',600,600,0)
insert into S  values ('201201','004',600,550,-50)
insert into S  values ('201201','005',5000,6000,1000)
insert into S  values ('201202','002',100,650,550)
insert into S  values ('201202','004',6000,16500,10500)
insert into S  values ('201203','001',6000,6500,500)
insert into S  values ('201203','002',5000,7000,2000)
insert into S  values ('201203','005',700,100,-600)
insert into S  values ('201203','006',8000,10500,2500)
go

--第一种方法。
select t.pname 供应商, sum(account) 总余额,
       sum(case when datediff(mm,pid+'01',getdate()) = 0 then account else 0 end) [1个月],
       sum(case when datediff(mm,pid+'01',getdate()) = 1 then account else 0 end) [2个月],
       sum(case when datediff(mm,pid+'01',getdate()) = 2 then account else 0 end) [3个月],
       sum(case when datediff(mm,pid+'01',getdate()) >= 3 then account else 0 end) [3个月以前]
from s t group by pname
/*
供应商        总余额                                      1个月                                      2个月                                      3个月                                      3个月以前                                    
---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 
001        1000.0                                   500.0                                    .0                                       500.0                                    .0
002        4550.0                                   2000.0                                   550.0                                    2000.0                                   .0
003        .0                                       .0                                       .0                                       .0                                       .0
004        10450.0                                  .0                                       10500.0                                  -50.0                                    .0
005        400.0                                    -600.0                                   .0                                       1000.0                                   .0
006        2500.0                                   2500.0                                   .0                                       .0                                       .0

（所影响的行数为 6 行）
*/

--第二种方法。
select t.pname 供应商, sum(account) 总余额,
       isnull((select sum(account) from s where pname = t.pname and  datediff(mm,pid+'01',getdate()) =