求一个简单的多次计算结果SQL
没有积分了，借贵帖求助。有一个发票表 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
TT on T.customer =TT.customer
left join T TTT on T.nn =TTT.nn -1 and
TT on T.yy =TTT.yy and
TT on T.customer =TTT.customer
;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
T.yy =TT.yy - 1 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
USE test
GO
--if object_id('t1') is not null
--drop table t1
--Go
----test data
--Create table t1([id] smallint PRIMARY KEY,[customer] nvarchar(2),[kaipiandate] datetime,[amout] smallint)
--Insert into t1
--Select 1,N'a1','2011-09-13',9000
--Union all Select 2,N'a2','2011-09-14',5000
--Union all Select 3,N'a3','2011-09-15',1000
--Union all Select 4,N'a1','2012-08-11',8000
--Union all Select 5,N'a2','2012-08-12',9900
--Union all Select 6,N'a3','2012-08-13',8800
--Union all Select 7,N'a1','2012-09-20',10000
--Union all Select 8,N'a2','2012-09-21',9700
--Union all Select 9,N'a3','2012-09-22',7500
DECLARE @SelectDate NVARCHAR(7),@Date DATETIME,@Sql NVARCHAR(MAX)
SET @SelectDate='2012-09'
SET @Date=CONVERT(DATETIME,@SelectDate+'-01')
SET @Sql=N'
SELECT
YEAR(@Datetime)AS 年份
,MONTH(@Datetime)AS 月份
,a.customerAS 客户代号
,ISNULL(b.TotalAmount,0)AS 开票金额
,ISNULL(c.TotalAmount,0)AS 上个月开票金额
,ISNULL(d.TotalAmount,0)AS [上年度当月('+LTRIM(MONTH(@Date))+N'月)开票金额]
FROM t1 AS a
OUTER APPLY(SELECT SUM(amout) AS TotalAmount FROM t1 AS x
WHERE a.customer=x.customer
AND DATEDIFF(mm,x.kaipiandate,@Datetime)=0
) AS b
OUTER APPLY(SELECT SUM(amout) AS TotalAmount FROM t1 AS x
WHERE a.customer=x.customer
AND DATEDIFF(mm,x.kaipiandate,@Datetime)=1
) AS c
OUTER APPLY(SELECT SUM(amout) AS TotalAmount FROM t1 AS x
WHERE a.customer=x.customer
AND DATEDIFF(mm,x.kaipiandate,@Datetime)=12
) AS d
GROUP BY a.customer,b.TotalAmount,c.TotalAmount,d.TotalAmount
ORDER BY b.TotalAmount desc,c.TotalAmount desc,d.TotalAmount desc
'
EXEC sys.sp_executesql @Sql,N'@Datetime datetime',@Datetime=@Date
;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
T.yy =TT.yy + 1 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(@Date) AS 年份
,MONTH(@Date) AS 月份
,a.customer AS 客户代号
,ISNULL(b.TotalAmount,0) AS 开票金额
,ISNULL(c.TotalAmount,0) AS 上个月开票金额
,ISNULL(d.TotalAmount,0) AS 上年度当月开票金额
FROM t1 AS a
OUTER APPLY(SELECT SUM(amout) AS TotalAmount FROM t1 AS x
WHERE a.customer=x.customer
AND DATEDIFF(mm,x.kaipiandate,@Date)=0
) AS b
OUTER APPLY(SELECT SUM(amout) AS TotalAmount FROM t1 AS x
WHERE a.customer=x.customer
AND DATEDIFF(mm,x.kaipiandate,@Date)=1
) AS c
OUTER APPLY(SELECT SUM(amout) AS TotalAmount FROM t1 AS x
WHERE a.customer=x.customer
AND DATEDIFF(mm,x.kaipiandate,@Date)=12
) AS d
GROUP BY a.customer,b.TotalAmount,c.TotalAmount,d.TotalAmount
ORDER BY b.TotalAmount desc,c.TotalAmount desc,d.TotalAmount desc
