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

试试这个：

With DateRange As (SELECt ID, DateFROM     d_DatesWHERe  (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 ADateUnion AllSelect ID + 1, ADate + 1FROM DateRange Where ADate < @inquiryEndDate)Select d.ID, d.Date, s.MortgageNumber, s.Investor_IDFrom DateRange d    Left Join f_MortgageSnapshots s        On d.ID = s.SnapshotDate_IDAnd  MortgageNumber = @inquiryMortgageNumberOPTION (MAXRECURSION 2000);

如果你想空行来显示一些默认值

MortgageNumber

Investor_ID

Coalesce()

With DateRange As (SELECt ID, inquiryStartDate ADateUnion AllSelect ID + 1, ADate + 1FROM DateRange Where ADate < @inquiryEndDate)Select d.ID, d.Date,    Coalesce(s.MortgageNumber, 'DefaultMortgageNumber') MortgageNumber,   Coalesce(s.Investor_ID , -1) Investor_IDFrom DateRange d    Left Join f_MortgageSnapshots s        On d.ID = s.SnapshotDate_IDAnd  MortgageNumber = @inquiryMortgageNumberOPTION (MAXRECURSION 2000);