Note! My goal is to help the reader to learn and not to provide final query as a solution for his homework. Therefore I will present the solution in two steps,and I will add several “PRINT” command in the dynamic solution,so the reader will have option to check the intermediate step in the work.

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,'2017-03-24',40,(3,66,'2018-08-15',100,'invoiced'),(4,77,'2018-12-02',25,'paid'),(5,'2018-09-10',35,(6,'2019-07-13','ordered')
GO

DROP TABLE IF EXISTS Forecast;
CREATE TABLE Forecast
    (ForecastID int,FDate date,Percentage int);
INSERT INTO Forecast
    (ForecastID,FDate,Percentage)
VALUES
    (1,'2020-12-01',100),'2023-05-17',25),'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

让我们首先展示一个简单的静态解决方案

这基于我们知道,对于Budget表中的每个RecordID,我们最多有三行,而对于Forecast表中的每个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
--where r.RecordID = 77
GO

Note! For static solution and without indexes (as I will add in the end),the above solution is VERY bad regarding performance,but once we add the right index and as a base for a dynamic solution this option should fit well.

现在我们可以呈现动态解决方案.

-- Get number of columns
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),b' + CONVERT(VARCHAR(2),@loop) + '.Status BStatus' + CONVERT(VARCHAR(2),'

    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),(f' + CONVERT(VARCHAR(2),@loop) + '.Percentage * r.BAmount)/100 FResult' + CONVERT(VARCHAR(2),'

    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,'
+ @sqlString1
+ N'
from Records r'
+ @sqlString2

print @sqlString

EXECUTE sp_executesql @sqlString
GO

IMPORTANT! This solution is not necessarily the one that gives the best performance,but the one that is probably the simplest to follow and understand. On production once we will have the real DDL+DML and the server will have the statistics,then we will be able to improve the performance and chose best solution for our specific case.

索引

Note! The above solution might lead to a lot of sorting of the data,and having the right indexes is HIGHLY important here! It is important to test several different options and select the best.

为了论坛(或者你称之为stackoverflow,我认为它不是一个讨论论坛界面,而是Q& A界面),我添加查询来创建CLUSTERED INDEX,我假设你在生产中有一个,并且可选NONCLUSTERED您应该测试的INDEX(我没有测试过其他选项,这意味着第一个出现在我脑海中,因此建议使用真正的DDL DML检查正确的索引).

-- CLUSTERED INDEX
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 

-- NONCLUSTERED INDEX
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