左外连接日期范围 CTE 不按我预期工作

2021-09-10 00:00:00 sql tsql sql-server

我正在使用以下 LOJ 查询，它使用 CTE 生成日期范围:

I'm using the following LOJ query which uses a CTE to generate a range of dates:

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;

我明白了:

但我想要这个:

我做错了什么?

请注意，f_MortgageSnapshots 表中只有 2 行用于抵押贷款 12345678.

Quick note, There are just 2 rows in the f_MortgageSnapshots table for mortgage 12345678.

声明@inquiryStartDate DateTime;声明@inquiryEndDate DateTime;声明@inquiryMortgageNumber nvarchar(50);

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

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

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;

推荐答案

试试这个:

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 时，您真的不需要点击日期表

Also, you really don't need to hit a date table when using CTEs

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() 函数:

If you want null rows to display some default value for MortgageNumber and Investor_ID, use the Coalesce() function:

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);

相关文章