問題描述

我正在使用以下 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);

這篇關于左外連接日期范圍 CTE 不按我預期工作的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！