你好,
注意!我的目标是帮助读者学习,而不是为他的作业提供最终查询解决方案。因此,我将分两步呈现解决方案,并在动态解决方案中添加几个“PRINT”命令,以便读者可以选择检查工作中的中间步骤。
OP提供的DDL+DML:
DROP TABLE IF EXISTS Budget;
CREATE TABLE Budget
(BudgetID int, RecordID int, BDate date,Percentage int, [Status] varchar(50));
INSERT INTO Budget
(BudgetID, RecordID, BDate,Percentage,Status)
VALUES
(1, 55, '2017-01-01', 60, 'ordered'),
(2, 55, '2017-03-24', 40, 'ordered'),
(3, 66, '2018-08-15', 100, 'invoiced'),
(4, 77, '2018-12-02', 25, 'paid'),
(5, 77, '2018-09-10', 35, 'ordered'),
(6, 77, '2019-07-13', 40, 'ordered')
GO
DROP TABLE IF EXISTS Forecast;
CREATE TABLE Forecast
(ForecastID int, RecordID int, FDate date, Percentage int);
INSERT INTO Forecast
(ForecastID, RecordID, FDate,Percentage)
VALUES
(1, 55, '2020-12-01', 100),
(2, 77, '2023-05-17', 25),
(3, 77, '2024-11-28', 75)
GO
DROP TABLE IF EXISTS Records;
CREATE TABLE Records
(RecordID int, BAmount int, FAmount int, Name varchar(40), Description varchar(40) )
;
INSERT INTO Records
(RecordID, BAmount,FAmount,Name, Description)
VALUES
(55, 15000, 33000, 'Prod1', 'Desc1' ),
(66, 22000, 17500, 'Prod2', 'Desc2' ),
(77, 40000, 44000, 'Prod3', 'Desc3' )
GO
select * from Budget
select * from Forecast
select * from Records
首先展示一个简单的静态解决方案
这基于我们知道预算表中每个RecordID最多有三行,而预测表中每个RecordID最多有两行。这将有助于理解我接下来要展示的动态解决方案。
;With CteBudget as (
select
b.BDate, b.BudgetID, b.Percentage, b.RecordID, b.Status
,RN = ROW_NUMBER() OVER (partition by b.RecordID order by b.BudgetID)
from Budget b
),
CteForecast as (
select
f.FDate, f.ForecastID, f.Percentage, f.RecordID
,RN = ROW_NUMBER() OVER (partition by f.RecordID order by f.ForecastID)
from Forecast f
)
select
r.RecordID, r.Name, r.Description,
b1.BDate BDate1, (b1.Percentage * r.BAmount)/100 BResult1, b1.Status BStatus1,
b2.BDate BDate2, (b2.Percentage * r.BAmount)/100 BResult2, b2.Status BStatus2,
b3.BDate BDate3, (b3.Percentage * r.BAmount)/100 BResult3, b3.Status BStatus3,
f1.FDate FDate1, (f1.Percentage * r.BAmount)/100 FResult1,
f2.FDate FDate2, (f2.Percentage * r.BAmount)/100 FResult2
from Records r
left join CteBudget b1 on r.RecordID = b1.RecordID and b1.RN = 1
left join CteBudget b2 on r.RecordID = b2.RecordID and b2.RN = 2
left join CteBudget b3 on r.RecordID = b3.RecordID and b3.RN = 3
left join CteForecast f1 on r.RecordID = f1.RecordID and f1.RN = 1
left join CteForecast f2 on r.RecordID = f2.RecordID and f2.RN = 2
GO
注意! 对于静态解决方案和没有索引(我会在最后添加),上述解决方案在性能方面非常差,但一旦我们添加正确的索引并作为动态解决方案的基础,这个选项应该适合。
现在我们可以提出动态解决方案。
Declare @NumBudget tinyint
Declare @NumForecast tinyint
SELECT @NumBudget = MAX(C) FROM (
select COUNT(RecordID) C
from Budget
GROUP BY RecordID
) t
SELECT @NumForecast = MAX(C) FROM (
select COUNT(RecordID) C
from Forecast
GROUP BY RecordID
) t
DECLARE @SQLString1 nvarchar(MAX) = '';
DECLARE @SQLString2 nvarchar(MAX) = '';
DECLARE @loop int = 1;
WHILE @loop <= @NumBudget BEGIN
SET @SQLString1 = @SQLString1 + N'
b' + CONVERT(VARCHAR(2),@loop) + '.BDate BDate' + CONVERT(VARCHAR(2),@loop) + ', (b' + CONVERT(VARCHAR(2),@loop) + '.Percentage * r.BAmount)/100 BResult' + CONVERT(VARCHAR(2),@loop) + ', b' + CONVERT(VARCHAR(2),@loop) + '.Status BStatus' + CONVERT(VARCHAR(2),@loop) + ', '
SET @SQLString2 = @SQLString2 + N'
left join CteBudget b' + CONVERT(VARCHAR(2),@loop) + ' on r.RecordID = b' + CONVERT(VARCHAR(2),@loop) + '.RecordID and b' + CONVERT(VARCHAR(2),@loop) + '.RN = 1'
SET @loop = @loop + 1
END
SET @loop = 1
WHILE @loop <= @NumForecast BEGIN
SET @SQLString1 = @SQLString1 + N'
f' + CONVERT(VARCHAR(2),@loop) + '.FDate FDate' + CONVERT(VARCHAR(2),@loop) + ', (f' + CONVERT(VARCHAR(2),@loop) + '.Percentage * r.BAmount)/100 FResult' + CONVERT(VARCHAR(2),@loop) + ','
SET @SQLString2 = @SQLString2 + N'
left join CteForecast f' + CONVERT(VARCHAR(2),@loop) + ' on r.RecordID = f' + CONVERT(VARCHAR(2),@loop) + '.RecordID and f' + CONVERT(VARCHAR(2),@loop) + '.RN = 1'
SET @loop = @loop + 1
END
SET @SQLString1 = STUFF (@SQLString1, LEN(@SQLString1) , 1 , '')
PRINT '/************************************************/'
PRINT @SQLString1
PRINT @SQLString2
PRINT '/************************************************/'
DECLARE @SQLString nvarchar(MAX);
SET @SQLString = N'
;With CteBudget as (
select
b.BDate, b.BudgetID, b.Percentage, b.RecordID, b.Status
,RN = ROW_NUMBER() OVER (partition by b.RecordID order by b.BudgetID)
from Budget b
),
CteForecast as (
select
f.FDate, f.ForecastID, f.Percentage, f.RecordID
,RN = ROW_NUMBER() OVER (partition by f.RecordID order by f.ForecastID)
from Forecast f
)
select
r.RecordID, r.Name, r.Description,
'
+ @SQLString1
+ N'
from Records r'
+ @SQLString2
print @SQLString
EXECUTE sp_executesql @SQLString
GO
重要提示! 这个解决方案不一定是性能最好的,但可能是最简单易懂的。在生产环境中,一旦我们有了真实的DDL+DML和服务器统计数据,就可以提高性能并选择最适合我们特定情况的解决方案。
索引
注意! 上述解决方案可能导致大量数据排序,因此正确的索引非常重要!测试几种不同的选项并选择最佳选项非常重要。
为了论坛的利益(或者你称之为stackoverflow的什么,但我认为它不是一个讨论论坛界面,而是一个问答界面),我添加了创建聚集索引的查询语句,我假设你在生产环境中拥有这个索引,以及一个可选的非聚集索引,你应该对其进行测试(我没有测试其他选项,这只是我想到的第一个,因此建议使用真实的DDL+DML查找正确的索引)。
CREATE CLUSTERED INDEX IX_Budget_BudgetID
ON dbo.Budget (BudgetID);
GO
CREATE CLUSTERED INDEX IX_Forecast_ForecastID
ON dbo.Forecast (ForecastID);
GO
CREATE CLUSTERED INDEX IX_Records_RecordID
ON dbo.Records (RecordID);
GO
CREATE NONCLUSTERED INDEX NX_Budget_RecordID_BudgetID
ON dbo.Budget (RecordID,BudgetID);
GO
CREATE NONCLUSTERED INDEX NX_Forecast_RecordID_ForecastID
ON dbo.Forecast (RecordID,ForecastID);
GO
CREATE NONCLUSTERED INDEX NX_Records_RecordID_RecordID
ON dbo.Records (RecordID);
GO