这个 账龄分析 如何做

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                                    .0002        4550.0                                   2000.0                                   550.0                                    2000.0                                   .0003        .0                                       .0                                       .0                                       .0                                       .0004        10450.0                                  .0                                       10500.0                                  -50.0                                    .0005        400.0                                    -600.0                                   .0                                       1000.0                                   .0006        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()) = 0),0) [1个月],       isnull((select sum(account) from s where pname = t.pname and  datediff(mm,pid+'01',getdate()) = 1),0) [2个月],       isnull((select sum(account) from s where pname = t.pname and  datediff(mm,pid+'01',getdate()) = 2),0) [3个月],       isnull((select sum(account) from s where pname = t.pname and  datediff(mm,pid+'01',getdate()) >= 3),0) [3个月以前]from s t group by pname/*供应商        总余额                                      1个月                                      2个月                                      3个月                                      3个月以前                                    ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 001        1000.0                                   500.0                                    .0                                       500.0                                    .0002        4550.0                                   2000.0                                   550.0                                    2000.0                                   .0003        .0                                       .0                                       .0                                       .0                                       .0004        10450.0                                  .0                                       10500.0                                  -50.0                                    .0005        400.0                                    -600.0                                   .0                                       1000.0                                   .0006        2500.0                                   2500.0                                   .0                                       .0                                       .0（所影响的行数为 6 行）*/drop table s