这个 账龄分析 怎么做

Question

本帖最后由 karxiu 于 2012-03-22 16:00:24 编辑

Answer 1

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()) = 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                                    .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 行）
*/drop table s

Answer 2

select pname,SUM(account),sum(case when pid='201201' then account else 0 end)
,sum(case when pid='201202' then account else 0 end)
,sum(case when pid='201203' then account else 0 end)
from dbo.S
group by pname