左外连接，日期范围CTE无法正常工作

小编典典

左外连接，日期范围CTE无法正常工作

sql

我正在使用以下LOJ查询，该查询使用CTE来生成日期范围：

Declare @inquiryStartDate DateTime;
Declare @inquiryEndDate DateTime;
Declare @inquiryMortgageNumber nvarchar(50);

SET @inquiryStartDate = '2013-07-01';
SET @inquiryEndDate = '2013-07-31';
SET @inquiryMortgageNumber = '12345678';

With DateRange As (
    SELECT ID, Date
    FROM     d_Dates
    WHERE  (Date BETWEEN @inquiryStartDate AND @inquiryEndDate)
)
Select DateRange.ID, DateRange.Date,f_MortgageSnapshots.MortgageNumber, f_MortgageSnapshots.Investor_ID
From DateRange
LEFT OUTER JOIN f_MortgageSnapshots On DateRange.ID = f_MortgageSnapshots.SnapshotDate_ID
WHERE f_MortgageSnapshots.MortgageNumber = @inquiryMortgageNumber;

解决方案在这里！

声明@inquiryStartDate DateTime; 声明@inquiryEndDate DateTime;声明@inquiryMortgageNumber nvarchar（50）;

SET @inquiryStartDate =‘2013-07-01’; SET @inquiryEndDate =‘2013-07-31’; SET@inquiryMortgageNumber =‘7078575’;

With DateRange As (
SELECT ID, d_Dates.Date
FROM     d_Dates
WHERE  (d_Dates.Date BETWEEN @inquiryStartDate AND @inquiryEndDate)
 )
Select DateRange.ID, DateRange.Date,f_MortgageSnapshots.MortgageNumber, f_MortgageSnapshots.Investor_ID
From DateRange Left Join f_MortgageSnapshots 
     On DateRange.ID = f_MortgageSnapshots.SnapshotDate_ID
        And  MortgageNumber = @inquiryMortgageNumber;

阅读 187

2021-05-16

共1个答案

小编典典

试试这个：

With DateRange As (
SELECT ID, Date
FROM     d_Dates
WHERE  (Date BETWEEN @inquiryStartDate AND @inquiryEndDate)
 )
   Select d.ID, d.Date, s.MortgageNumber, s.Investor_ID
   From DateRange d
      Left Join f_MortgageSnapshots s
         On d.ID = s.SnapshotDate_ID
            And  MortgageNumber = @inquiryMortgageNumber;

另外，使用CTE时，您真的不需要点击日期表

With DateRange As (
SELECT ID, inquiryStartDate ADate
Union All
Select ID + 1, ADate + 1
FROM DateRange 
Where ADate < @inquiryEndDate)
Select d.ID, d.Date, s.MortgageNumber, s.Investor_ID
From DateRange d
    Left Join f_MortgageSnapshots s
        On d.ID = s.SnapshotDate_ID
           And  MortgageNumber = @inquiryMortgageNumber
OPTION (MAXRECURSION 2000);

如果你想空行来显示一些默认值MortgageNumber和Investor_ID使用的Coalesce() 功能：

With DateRange As (
SELECT ID, inquiryStartDate ADate
Union All
Select ID + 1, ADate + 1
FROM DateRange 
Where ADate < @inquiryEndDate)
Select d.ID, d.Date, 
   Coalesce(s.MortgageNumber, 'DefaultMortgageNumber') MortgageNumber,
   Coalesce(s.Investor_ID , -1) Investor_ID
From DateRange d
    Left Join f_MortgageSnapshots s
        On d.ID = s.SnapshotDate_ID
           And  MortgageNumber = @inquiryMortgageNumber
OPTION (MAXRECURSION 2000);

2021-05-16