没有积分了，借贵帖求助。有一个发票表 fapiao，记录了最近几年的开票数据
发票号 客户代码 开票时间 开票金额
id customer kaipiandate amout
请教，如何实现如下功用：
比如统计2012年9月份的客户开票数据，按开票金额从多到少排列下来，需要显示如下结果
年份 月份 客户代码 开票金额 上个月开票金额 上年度当月（9月）开票金额
2012 9 S001 10000 8000 9000
2012 9 S054 9700 9900 5000
2012 9 S009 7500 8800 10000
------最佳解决方案--------------------
;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
TT on T.yy =TT.yy - 1 and