如何实现SQL楼梯查询?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (26)

我需要一些帮助产生一个MS SQL 2012查询,将匹配所需的阶梯步骤输出。行按一个日期范围(帐户提交日期月)汇总数据,列按另一个日期范围(付款日期月)对数据进行汇总。

表1:帐目跟踪为收款而放置的帐户。

CREATE TABLE [dbo].[Accounts](
    [AccountID] [nchar](10) NOT NULL,
    [SubmissionDate] [date] NOT NULL,
    [Amount] [money] NOT NULL,
CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED (AccountID ASC))

INSERT INTO [dbo].[Accounts] VALUES ('1000', '2012-01-01', 1999.00)
INSERT INTO [dbo].[Accounts] VALUES ('1001', '2012-01-02', 100.00)
INSERT INTO [dbo].[Accounts] VALUES ('1002', '2012-02-05', 350.00)
INSERT INTO [dbo].[Accounts] VALUES ('1003', '2012-03-01', 625.00)
INSERT INTO [dbo].[Accounts] VALUES ('1004', '2012-03-10', 50.00)
INSERT INTO [dbo].[Accounts] VALUES ('1005', '2012-03-10', 10.00)

表2:

CREATE TABLE [dbo].[Trans](
    [TranID] [int] IDENTITY(1,1) NOT NULL,
    [AccountID] [nchar](10) NOT NULL,
    [TranDate] [date] NOT NULL,
    [TranAmount] [money] NOT NULL,
CONSTRAINT [PK_Trans] PRIMARY KEY CLUSTERED (TranID ASC))

INSERT INTO [dbo].[Trans] VALUES (1000, '2012-01-15', 300.00)
INSERT INTO [dbo].[Trans] VALUES (1000, '2012-02-15', 300.00)
INSERT INTO [dbo].[Trans] VALUES (1000, '2012-03-15', 300.00)
INSERT INTO [dbo].[Trans] VALUES (1002, '2012-02-20', 325.00)
INSERT INTO [dbo].[Trans] VALUES (1002, '2012-04-20', 25.00)
INSERT INTO [dbo].[Trans] VALUES (1003, '2012-03-24', 625.00)
INSERT INTO [dbo].[Trans] VALUES (1004, '2012-03-28', 31.00)
INSERT INTO [dbo].[Trans] VALUES (1004, '2012-04-12', 5.00)
INSERT INTO [dbo].[Trans] VALUES (1005, '2012-04-08', 7.00)
INSERT INTO [dbo].[Trans] VALUES (1005, '2012-04-28', 3.00)

以下是所需输出:

                                 *Total Payments in Each Month*
SubmissionYearMonth TotalAmount | 2012-01  2012-02  2012-03  2012-04 
--------------------------------------------------------------------
2012-01             2099.00     |  300.00   300.00   300.00     0.00
2012-02             350.00      |           325.00     0.00    25.00
2012-03             685.00      |                    656.00    15.00

前两列和帐户。按月分组的金额。

最后4列是Tran的总和。按月计算,用于当前行的给定月份中放置的帐户。

我一直在处理的查询感觉很接近。我只是没有正确的滞后。下面是我目前正在处理的查询:

Select SubmissionYearMonth, 
       TotalAmount,
       pt.[0] AS MonthOld0,
       pt.[1] AS MonthOld1,
       pt.[2] AS MonthOld2,
       pt.[3] AS MonthOld3,
       pt.[4] AS MonthOld4,
       pt.[5] AS MonthOld5,
       pt.[6] AS MonthOld6,
       pt.[7] AS MonthOld7,
       pt.[8] AS MonthOld8,
       pt.[9] AS MonthOld9,
       pt.[10] AS MonthOld10,
       pt.[11] AS MonthOld11,
       pt.[12] AS MonthOld12,
       pt.[13] AS MonthOld13

From (
       SELECT Convert(Char(4),Year(SubmissionDate)) + '-' + Right('00' + Convert(VarChar(2), DatePart(Month, SubmissionDate)),2) AS SubmissionYearMonth, 
       SUM(Amount) AS TotalAmount
       FROM   Accounts
       GROUP BY Convert(Char(4),Year(SubmissionDate)) + '-' + Right('00' + Convert(VarChar(2), DatePart(Month, SubmissionDate)),2)
     ) 
AS AccountSummary
OUTER APPLY
(
SELECT *
FROM (
       SELECT CASE WHEN DATEDIFF(Month, SubmissionDate, TranDate) < 13
                   THEN DATEDIFF(Month, SubmissionDate, TranDate)
                   ELSE 13
              END AS PaymentMonthAge,
              TranAmount
       FROM Trans INNER JOIN Accounts ON Trans.AccountID = Accounts.AccountID
       Where Convert(Char(4),Year(TranDate)) + '-' + Right('00' + Convert(VarChar(2), DatePart(Month, TranDate)),2)
             = AccountSummary.SubmissionYearMonth
       ) as TransTemp
       PIVOT (SUM(TranAmount)
              FOR PaymentMonthAge IN ([0],
                                      [1],
                                      [2],
                                      [3],
                                      [4],
                                      [5],
                                      [6],
                                      [7],
                                      [8],
                                      [9],
                                      [10],
                                      [11],
                                      [12],
                                      [13])) as TransPivot
) as pt

它正在产生以下输出:

SubmissionYearMonth TotalAmount MonthOld0 MonthOld1 MonthOld2 MonthOld3 ...
2012-01             2099.00     300.00    NULL      NULL      NULL      ...
2012-02             350.00      325.00    300.00    NULL      NULL      ...
2012-03             685.00      656.00    NULL      300.00    NULL      ...

提问于
用户回答回答于

 select a.yyyymm, a.Amount,
        t201201, t201202, t201203, t201204
 from (select LEFT(convert(varchar(255), a.submissiondate, 121), 7) as yyyymm,
              SUM(a.Amount) as amount
       from Accounts a
       group by  LEFT(convert(varchar(255), a.submissiondate, 121), 7) 
      ) a left outer join
      (select LEFT(convert(varchar(255), a.submissiondate, 121), 7) as yyyymm,
              sum(case when trans_yyyymm = '2012-01' then tranamount end) as t201201,
              sum(case when trans_yyyymm = '2012-02' then tranamount end) as t201202,
              sum(case when trans_yyyymm = '2012-03' then tranamount end) as t201203,
              sum(case when trans_yyyymm = '2012-04' then tranamount end) as t201204
       from Accounts a join
            (select t.*, LEFT(convert(varchar(255), t.trandate, 121), 7) as trans_yyyymm
             from trans t
            ) t
            on a.accountid = t.accountid
       group by LEFT(convert(varchar(255), a.submissiondate, 121), 7)
      ) t
      on a.yyyymm = t.yyyymm
 order by 1

当两个单元格中有一个0.00的时候,我得到一个空值。

用户回答回答于

With SubmissionMonths As
  (
  Select DateAdd(d, -Day(A.SubmissionDate) + 1, A.SubmissionDate) As SubmissionMonth
    , A.Amount
  From dbo.Accounts As A
  )
  , TranMonths As
  (
  Select DateAdd(d, -Day(Min( T.TranDate )) + 1, Min( T.TranDate )) As TranMonth
      , 1 As MonthNum
  From dbo.Accounts As A
    Join dbo.Trans As T
      On T.AccountId = A.AccountId
    Join SubmissionMonths As M
      On A.SubmissionDate >= M.SubmissionMonth
        And A.SubmissionDate < DateAdd(m,1,SubmissionMonth)
  Union All
  Select DateAdd(m, 1, TranMonth), MonthNum + 1
  From TranMonths
  Where MonthNum < 12
  )
  , TotalBySubmissionMonth As
  (
  Select M.SubmissionMonth, Sum( M.Amount ) As Total
  From SubmissionMonths As M
  Group By M.SubmissionMonth
  )
Select Format(SMT.SubmissionMonth,'yyyy-MM') As SubmissionMonth, SMT.Total
  , Sum( Case When TM.MonthNum = 1 Then T.TranAmount End ) As Month1
  , Sum( Case When TM.MonthNum = 2 Then T.TranAmount End ) As Month2
  , Sum( Case When TM.MonthNum = 3 Then T.TranAmount End ) As Month3
  , Sum( Case When TM.MonthNum = 4 Then T.TranAmount End ) As Month4
  , Sum( Case When TM.MonthNum = 5 Then T.TranAmount End ) As Month5
  , Sum( Case When TM.MonthNum = 6 Then T.TranAmount End ) As Month6
  , Sum( Case When TM.MonthNum = 7 Then T.TranAmount End ) As Month7
  , Sum( Case When TM.MonthNum = 8 Then T.TranAmount End ) As Month8
  , Sum( Case When TM.MonthNum = 9 Then T.TranAmount End ) As Month9
  , Sum( Case When TM.MonthNum = 10 Then T.TranAmount End ) As Month10
  , Sum( Case When TM.MonthNum = 11 Then T.TranAmount End ) As Month11
  , Sum( Case When TM.MonthNum = 12 Then T.TranAmount End ) As Month12
From TotalBySubmissionMonth As SMT
  Join dbo.Accounts As A
    On A.SubmissionDate >= SMT.SubmissionMonth
      And A.SubmissionDate < DateAdd(m,1,SMT.SubmissionMonth)
  Join dbo.Trans As T
    On T.AccountId = A.AccountId
  Join TranMonths As TM
    On T.TranDate >= TM.TranMonth
      And T.TranDate < DateAdd(m,1,TM.TranMonth)
Group By SMT.SubmissionMonth, SMT.Total

SQLFiddle版本

扫码关注云+社区