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