日期:2014-05-18  浏览次数:20485 次

在线等:求一条SQL语句!!
问题是这样的:
一个表 fy(费用表),字段有:fyje(费用金额),dxje (兑现金额) ,wlh(往来户),fyrq(费用日期),dxrq(兑现日期)
同一个往来户(wlh) 有N条费用。
例如:
wlh, fyje, dxje , fyrq, dxrq
泛洋 250 250 2007-05-01 2007-09-12
泛洋 100 100 2007-09-11 2007-09-12
泛洋 150 150 2007-09-12 2007-10-25
泛洋 200 200 2007-09-15 2007-10-25
只要dxrq 大于fyrq 表示费用已经兑现了 fyje就是 兑现金额
现在要一条这样的语句:
求往来户的所有金额,1<(dxrq-fyrq)<30天的兑现金额,(30<(dxrq-fyrq)<60的兑现金额,60<(fxrq-fyrq)的金额



------解决方案--------------------
乱写的,可能结果不对,但是我觉得应该可以这样写
------解决方案--------------------
这样写就ok了

select distinct Sum(dxje) as sumtotal, 
(select Sum(dxje) 
from fy 
where datediff(dd, fyrq, dxrq) between 1 and 30 ) as '1-30',
(select Sum(dxje) 
from fy 
where datediff(dd, fyrq, dxrq) between 31 and 60 ) as '31-60' ,
(select Sum(dxje) 
from fy 
where datediff(dd, fyrq, dxrq) >60 ) as '>60'
 from fy 

------解决方案--------------------
晕 应该这样 
select distinct wlh,Sum(fyje) as sumtotal,
(select Sum(dxje)
from fy
where wlh = a.wlh and datediff(dd, fyrq, dxrq) between 1 and 30) as '1-30 ',
(select Sum(dxje)
from fy
where wlh = a.wlh and datediff(dd, fyrq, dxrq) between 31 and 60) as '31-60 ',
(select Sum(dxje)
from fy
where wlh = a.wlh and datediff(dd, fyrq, dxrq) >60) as ' >60 ' 
from fy a 
group by Wlh
------解决方案--------------------
select wlh,
sum(case when datediff(dd,fyrq,dxrq)<=1 then 0 when datediff(dd,fyrq,dxrq)>=30 then o else fyje end) as '30天内的兑现金额',
sum(case when datediff(dd,fyrq,dxrq)<=30 then 0 when datediff(dd,fyrq,dxrq)>=60 then o else fyje end) as '30天到60天内的兑现金额',
sum(case when datediff(dd,fyrq,dxrq)>=60 then 0 else fyje end) as '60天内的兑现金额'
from ty
group by wlh
order by wlh