读书人
栏目▼

简单的多次计算结果SQL

发布时间： 2012-12-14 10:33:07 作者： rapoo

求一个简单的多次计算结果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


[其他解释]
引用:
SQL code
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)……


不行啊！！
[其他解释]
你给点测试数据嘛 fapiao表的测试数据
[其他解释]
InvoiceIDInvoiceDateCustomerNoTotalAmount
N100100012010/1/11 0:0025014791
N100100022010/1/11 0:0012828751.4
N100100032010/1/12 0:00152240000
N100100042010/1/25 0:001614575000
N100100052010/1/25 0:0034698620
N100100062010/1/25 0:001552854600
N100100072010/1/25 0:0056241572.59
N100100082010/1/25 0:003061280
N100100092010/1/25 0:0015010699.2
N100100102010/1/25 0:00130311665.89
N100100112010/1/25 0:00155936136.8
N100100122010/1/25 0:001713993.5
N100100132010/1/25 0:0034831792.1
N100200012010/2/8 0:00250100694.4
N100200022010/2/8 0:005739756
N100200032010/2/8 0:00432107020
N100200042010/2/8 0:0015025260
N100200052010/2/8 0:0036015000
N100300842010/2/25 0:001611025000
N100300852010/2/25 0:0046113915.5
N100300862010/2/25 0:004633133.9
N100300872010/2/25 0:001503500
N100300882010/2/25 0:00196102198
N100300892010/2/25 0:00516198437.6
N100300902010/2/25 0:0038130300

以上为测试数据。。那个时间应该是2010-01-01格式的。
[其他解释]
引用:

哦，不知道是不是不支持某些函数的原因，我换个函数试下
[其他解释]
引用:
SQL code
;with T as
( select datepart(year,kaipiandate)as nn,
datepart(month,kaipiandate)as yy,
customer,
sum(amout)as je
from fapiao
group by
datepart(ye……

sum(TTT.je) TT on T.yy 提示有错
[其他解释]
报什么错？大致是这样，没测试的
;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
引用:
引用:
SQL code
;with T as
( select datepart(year,kaipiandate)as nn,
datepart(month,kaipiandate)as yy,
customer,
sum(amout)as je
from fapiao
group by
datepart(ye……

sum(TTT.je) TT on T……

[其他解释]
楼上的大哥，查询可以显示结果了，但是有点问题，上面的查询我在T里加了一个年份和客户代码的查询条件，
显示出如下结果：
nnyyCustomerNoje上月份金额 上年度金额
201211117041398.51003505675.6600NULL
201221113505675.66003447611.0100NULL
201231113447611.01004691210.6400NULL
201241114691210.64004002935.5200NULL
201251114002935.52003495063.7200NULL
201261113495063.72003179836.6100NULL
201271113179836.61002443499.1500NULL
201281112443499.15002408018.9600NULL
201291112408018.96002765801.4300NULL
2012101112765801.4300NULL NULL
问题来了，上个月的开票金额显示好像反了。显示成下个月的开票金额了，上年度当月的金额都成NULL。。
[其他解释]
把月份改为+1，可以正常显示，现在的最要问题就是上年度当月的不显示了。
from T left join T TT on T.nn =TT.nn and
T.yy =TT.yy + 1 and
T.customer =TT.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


[其他解释]
引用:
SQL code
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,[amou……

能否改成一般的查询啊！
[其他解释]
;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

引用:
把月份改为+1，可以正常显示，现在的最要问题就是上年度当月的不显示了。
from T left join T TT on T.nn =TT.nn and
T.yy =TT.yy + 1 and
T.customer =TT.customer


[其他解释]
不用动态的话 "上年当月(9月)金额" 这个字段没办法自动替换
引用:
引用:

DECLARE @SelectDate NVARCHAR(7),@Date DATETIME,@Sql NVARCHAR(MAX)

SET @SelectDate='2012-09'

SET @Date=CONVERT(DATETIME,@SelectDate+'-01')


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


[其他解释]
引用:
SQL code
;with T as
( select datepart(year,kaipiandate)as nn,
datepart(month,kaipiandate)as yy,


customer,
sum(amout)as je
from fapiao
group by
datepart(ye……


差不多就要成功了，现在最后一个问题是2012年1月的一个月金额是空， 正常应该显示2011年12月份的金额。能否帮修改一下呢！
[其他解释]
加个判断就行了嘛

引用:
引用:
SQL code
;with T as
( select datepart(year,kaipiandate)as nn,
datepart(month,kaipiandate)as yy,
customer,
sum(amout)as je
from fapiao
group by
datepart(ye……

差不多就要成功了，现在最后一个问题是……

[其他解释]
哈哈，那个条件自己加，我用0替代了

读书人网 >SQL Server

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net