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

Left Outer Join With Date Range CTE Not Working As I Expect(左外连接日期范围 CTE 不按我预期工作)
本文介绍了左外连接日期范围 CTE 不按我预期工作的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

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

如果您希望空行显示 MortgageNumberInvestor_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 不按我预期工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Convert varchar to float IF ISNUMERIC(将 varchar 转换为 float IF ISNUMERIC)
multi part identifier could not be bound sql(无法绑定多部分标识符 sql)
Any benefit to explicitly dropping local temporary tables at the end of a stored procedure?(在存储过程结束时显式删除本地临时表有什么好处?)
Check if a column contains text using SQL(使用 SQL 检查列是否包含文本)
Select value if condition in SQL Server(SQL Server 中的条件选择值)
Control flow in T-SQL SP using IF..ELSE IF - are there other ways?(使用 IF..ELSE IF 在 T-SQL SP 中控制流 - 还有其他方法吗?)