请教高手一个SQL算法，谢谢大家了！！！
一个关于账龄计算的问题，下面是源数据表
客户单据类型余额 单据日期 账龄
A应收1292301 2010-1-31 00:00121
A应收1139292 2010-2-28 00:0093
A应收1152316.8 2010-3-23 00:0070
A应收78148.8 2010-3-23 00:0070
A应收1230465.6 2010-4-28 00:0034
A应收1204416 2010-5-25 00:007
A收款-1204416 2010-2-28 00:0093
A收款-1139292 2010-3-31 00:0062
A收款-1230465.62010-4-30 00:0032
A收款-1230465.62010-5-31 00:001
B应收196668 2010-1-31 00:00121
B应收8866 2010-2-25 00:0096
B应收154677.6 2010-2-28 00:0093
B应收508741.2 2010-3-23 00:0070
B应收453157.2 2010-4-28 00:0034
B应收411469.2 2010-5-25 00:007
B收款-196668 2010-3-31 00:0062
B收款-154677.6 2010-4-30 00:0032
B收款-8866 2010-4-30 00:0032
B收款-508741.2 2010-5-31 00:001
C应收506268 2010-1-21 00:00131
C应收536491.2 2010-2-25 00:0096
C应收633080 2010-3-23 00:0070
C应收426927.2 2010-4-19 00:0043
C应收913399.2 2010-5-17 00:0015
C应收959968.8 2009-12-30 00:00153
C应收584511.2 2009-9-30 00:00244
C应收974943.2 2009-4-30 00:00397
C应收45332.82 2009-1-23 00:00494
需要得到以下结果
客户应收余额账期内本币金额1-30天余额30--60天余额61-90天余额91-180天余额181-270天余额271-360天余额361-720天余额721天以上余额
A12923010120441687885000000
B864626.40411469.2453157.2000000
C5580921.620913399.2426927.26330802002728584511.201020276.020
SQL 语句怎么写啊，请求高手解答
[解决办法]
- SQL code
--测试环境if object_id('[tb]') is not null drop table [tb]go create table [tb]([客户] varchar(1),[单据类型] varchar(4),[余额] numeric(10,1),[单据日期] datetime,账龄 int)insert [tb]select 'A','应收','1292301','2010-1-31 00:00','121' union allselect 'A','应收','1139292','2010-2-28 00:00','93' union allselect 'A','应收','1152316.8','2010-3-23 00:00','70' union allselect 'A','应收','78148.8','2010-3-23 00:00','70' union allselect 'A','应收','1230465.6','2010-4-28 00:00','34' union allselect 'A','应收','1204416','2010-5-25 00:00','7' union allselect 'A','收款','-1204416','2010-2-28 00:00','93' union allselect 'A','收款','-1139292','2010-3-31 00:00','62' union allselect 'A','收款','-1230465.6','2010-4-30 00:00','32' union allselect 'A','收款','-1230465.6','2010-5-31 00:00','1' union allselect 'B','应收','196668','2010-1-31 00:00','121' union allselect 'B','应收','8866','2010-2-25 00:00','96' union allselect 'B','应收','154677.6','2010-2-28 00:00','93' union allselect 'B','应收','508741.2','2010-3-23 00:00','70' union allselect 'B','应收','453157.2','2010-4-28 00:00','34' union allselect 'B','应收','411469.2','2010-5-25 00:00','7' union allselect 'B','收款','-196668','2010-3-31 00:00','62' union allselect 'B','收款','-154677.6','2010-4-30 00:00','32' union allselect 'B','收款','-8866','2010-4-30 00:00','32' union allselect 'B','收款','-508741.2','2010-5-31 00:00','1' union allselect 'C','应收','506268','2010-1-21 00:00','131' union allselect 'C','应收','536491.2','2010-2-25 00:00','96' union allselect 'C','应收','633080','2010-3-23 00:00','70' union allselect 'C','应收','426927.2','2010-4-19 00:00','43' union allselect 'C','应收','913399.2','2010-5-17 00:00','15' union allselect 'C','应收','959968.8','2009-12-30 00:00','153' union allselect 'C','应收','584511.2','2009-9-30 00:00','244' union allselect 'C','应收','974943.2','2009-4-30 00:00','397' union allselect 'C','应收','45332.82','2009-1-23 00:00','494' goselect 客户,sum(余额)as 应收余额,0 as 账期内本币金额,case when sum(case when 单据类型='应收'and 账龄 > 30 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 1 and 31 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 31 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 1 and 30 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 30 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 1 and 30 then 余额 else 0 end) endend as [1-30天余额],case when sum(case when 单据类型='应收'and 账龄 > 60 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 31 and 60 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 60 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 31 and 60 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 60 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 31 and 60 then 余额 else 0 end) endend as[31-60天余额],case when sum(case when 单据类型='应收'and 账龄 > 90 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 61 and 90 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 90 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 61 and 90 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 90 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 61 and 90 then 余额 else 0 end) endend as[61-90天余额],case when sum(case when 单据类型='应收'and 账龄 > 180 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 91 and 180 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 180 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 91 and 180 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 180 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 91 and 180 then 余额 else 0 end) endend as[91-180天余额],case when sum(case when 单据类型='应收'and 账龄 > 270 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 181 and 270 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 270 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 181 and 270 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 270 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 181 and 270 then 余额 else 0 end) endend as[181-270天余额],case when sum(case when 单据类型='应收'and 账龄 > 360 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 271 and 360 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 360 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 271 and 360 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 360 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 271 and 360 then 余额 else 0 end) endend as[271-360天余额],case when sum(case when 单据类型='应收'and 账龄 > 720 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 361 and 720 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 720 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 361 and 720 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 360 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 361 and 720 then 余额 else 0 end) endend as[361-720天余额],case when sum(case when 单据类型='应收'and 账龄 > 720 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 > 720 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) else 0end as[721天以上余额]from tbgroup by 客户--结果/*客户 应收余额 账期内本币金额 1-30天余额 31-60天余额 61-90天余额 91-180天余额 181-270天余额 271-360天余额 361-720天余额 721天以上余额---- ------------ ----------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------A 1292301.0 0 1204416.0 87885.0 0.0 0.0 0.0 0.0 0.0 0.0B 864626.4 0 411469.2 453157.2 0.0 0.0 0.0 0.0 0.0 0.0C 5580921.6 0 913399.2 426927.2 633080.0 2002728.0 584511.2 0.0 1020276.0 0.0(3 行受影响)*/
[解决办法]
if exists(select * from sysobjects where [name]='tb')
drop table tb
create table tb
(
kh varchar(50),
djlx varchar(10),
ye float,
djrq datetime,
zl varchar(20)
)
go
insert into tb(kh,djlx,ye,djrq,zl)
(
select 'A','应收','1292301','2010-1-31 00:00','121' union all
select 'A','应收','1139292','2010-2-28 00:00','93' union all
select 'A','应收','1152316.8','2010-3-23 00:00','70' union all
select 'A','应收','78148.8','2010-3-23 00:00','70' union all
select 'A','应收','1230465.6','2010-4-28 00:00','34' union all
select 'A','应收','1204416','2010-5-25 00:00','7' union all
select 'A','收款','-1204416','2010-2-28 00:00','93' union all
select 'A','收款','-1139292','2010-3-31 00:00','62' union all
select 'A','收款','-1230465.6','2010-4-30 00:00','32' union all
select 'A','收款','-1230465.6','2010-5-31 00:00','1' union all
select 'B','应收','196668','2010-1-31 00:00','121' union all
select 'B','应收','8866','2010-2-25 00:00','96' union all
select 'B','应收','154677.6','2010-2-28 00:00','93' union all
select 'B','应收','508741.2','2010-3-23 00:00','70' union all
select 'B','应收','453157.2','2010-4-28 00:00','34' union all
select 'B','应收','411469.2','2010-5-25 00:00','7' union all
select 'B','收款','-196668','2010-3-31 00:00','62' union all
select 'B','收款','-154677.6','2010-4-30 00:00','32' union all
select 'B','收款','-8866','2010-4-30 00:00','32' union all
select 'B','收款','-508741.2','2010-5-31 00:00','1' union all
select 'C','应收','506268','2010-1-21 00:00','131' union all
select 'C','应收','536491.2','2010-2-25 00:00','96' union all
select 'C','应收','633080','2010-3-23 00:00','70' union all
select 'C','应收','426927.2','2010-4-19 00:00','43' union all
select 'C','应收','913399.2','2010-5-17 00:00','15' union all
select 'C','应收','959968.8','2009-12-30 00:00','153' union all
select 'C','应收','584511.2','2009-9-30 00:00','244' union all
select 'C','应收','974943.2','2009-4-30 00:00','397' union all
select 'C','应收','45332.82','2009-1-23 00:00','494'
)
go
select * from tb
go
select kh,sum(ye) '应收余额',0 as '账期内本币金额',
'1-30天余额'=sum(case when zl>=1 and zl<30 then ye else 0 end),
'30-60天余额'=sum(case when zl>=30 and zl<60 then ye else 0 end),
'61-90天余额'=sum(case when zl>60 and zl<=90 then ye else 0 end),
'91-180天余额'=sum(case when zl>90 and zl<=180 then ye else 0 end),
'181-270天余额'=sum(case when zl>180 and zl<=270 then ye else 0 end),
'271-360天余额'=sum(case when zl>270 and zl<=360 then ye else 0 end),
'361-720天余额'=sum(case when zl>360 and zl<=720then ye else 0 end),
'721天以上余额'=sum(case when zl>720 then ye else 0 end)
from tb group by kh
go