如何实现SQL楼梯查询？内容来源于 Stack Overflow，并遵循CC BY-SA 3.0许可协议进行翻译与使用
内容来源于 Stack Overflow，并遵循CC BY-SA 3.0许可协议进行翻译与使用
- 回答 (2)
- 关注 (0)
- 查看 (27)
我需要一些帮助产生一个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
扫码关注云+社区