日期:2014-05-17 浏览次数:20543 次
;with T as
( select datepart(year,kaipiandate)as nn,
datepart(month,kaipiandate)as yy,
customer,
sum(amout)as je
from fapiao
group by
datepart(year,kaipiandate),
datepart(month,kaipiandate),
customer )
select T.*,
TT.je as '上月金额',
TTT.je as '上年当月金额'
from T left join T TT on T.nn =TT.nn and
(case when T.yy=1 then 0 else T.yy =TT.yy + 1 end) and
T.customer =TT.customer
left join T TTT on T.nn =TTT.nn + 1 and
T.yy =TTT.yy and
T.customer =TTT.customer
select YEAR(a.kaipiandate),MONTH(a.kaipiandate),a.customer,a.amout,b.amout,c.amout
from fapiao a
inner join fapiao b on YEAR(b.kaipiandate)=YEAR(a.kaipiandate) and MONTH(b.kaipiandate)=MONTH(a.kaipiandate)-1 and b.customer=a.customer
inner join fapiao c on YEAR(c.kaipiandate)=YEAR(a.kaipiandate)-1 and MONTH(c.kaipiandate)=MONTH(a.kaipiandate) and c.customer=a.customer
order by a.amout desc
;with T as
( select datepart(year,kaipiandate)as nn,
datepart(month,kaipiandate)as yy,
customer,
sum(amout)as je
from fapiao
group by
datepart(year,kaipiandate),
datepart(month,kaipiandate),
customer )
select *,TT.je as '上月金额',sum(TTT.je) as '上年当月金额'
from T left join T TT on T.nn =TT.nn and