列名"Created_Date"不明确？

Question

我使用SQL SSMS。由于以下原因，SQL提示错误。在我对许多其他内部连接使用相同的脚本之后，仍然存在以下错误。我试着修改我的脚本，我一直得到相同的消息:

Msg 209, Level 16, State 1, Line 9 Ambiguous column name 'Created_Date'.
Msg 209, Level 16, State 1, Line 17 Ambiguous column name 'Created_Date'.
Msg 209, Level 16, State 1, Line 18 Ambiguous column name 'Created_Date'.
Msg 209, Level 16, State 1, Line 19 Ambiguous column name 'Created_Date'.
Msg 209, Level 16, State 1, Line 20 Ambiguous column name 'Created_Date'.

(脚本)

SELECT 
rs.Resident_ID, 
[UserName] = rsdt.First_Name + ' ' + rsdt.Last_Name,
in.Invoice_Amount,
in.Due_Date as due,
in.Created_Date,
DATEDIFF(day, [Created_Date], Getdate()) as " Number_of_Days ",
in.Paid_Date as paid,
in.Description,  
ar.Payment_Type, 
ar.Receipt_Descriptions,
CASE 
WHEN DATEDIFF(day, [Created_Date], Getdate()) Between 90 AND 119 Then '90 days'
WHEN DATEDIFF(day, [Created_Date], Getdate()) Between 120 AND 179 Then '120 days'
WHEN DATEDIFF(day, [Created_Date], Getdate()) Between 180 AND 364 Then '180 days'
WHEN DATEDIFF(day, [Created_Date], Getdate()) >= 365 Then '365+ days'
END As [Days Outstanding]
FROM dbo.ar_receipts AS ar
INNER JOIN dbo.residents AS rs
ON ar.Resident_ID = rs.Resident_ID 
INNER JOIN dbo.invoices AS in
ON ar.Created_Date = in.Created_Date
WHERE
DATEDIFF(Day, in.Created_Date, GETDATE ()) > = 90
AND in.Created_Date >= DATEADD(MONTH, -48, GETDATE())
Order by in.Created_Date

Answer 1

你有:

CASE WHEN DATEDIFF(day, [Created_Date], Getdate()) Between 90 AND 119 

但是错误消息很清楚:查询中不止一个表有一个名为Created_Date的列。因此，您需要使用您创建的表别名来告诉SQL Server您指的是哪一个。假设它来自dbo.invoices，所以:

CASE WHEN DATEDIFF(day, in.[Created_Date], Getdate()) Between 90 AND 119 

虽然in也是一个糟糕的别名选择，因为像double一样，它是一个保留词/关键字，至少会在各种编辑器中亮起，或者在某些上下文中导致语法错误。

更重要的是:

DATEDIFF(day, in.[Created_Date], Getdate()) >= 365 

更有效的写法是:

in.Created_Date < DATEADD(DAY, -365, CONVERT(date, GETDATE())

这是一些额外的字符，我知道，但至少有一个使用索引的机会。

同样，如果您以相反的方式遍历(从最旧的类别到最新的类别)，您根本不必处理BETWEEN场景，因为第一个匹配的场景将使其余的比较无操作:

CASE
WHEN in.Created_Date < DATEADD(DAY, -365, CONVERT(date, GETDATE())
THEN '365+ Days'
WHEN in.Created_Date < DATEADD(DAY, -180, CONVERT(date, GETDATE())
THEN '180 Days'
WHEN in.Created_Date < DATEADD(DAY, -120, CONVERT(date, GETDATE())
THEN '120 Days'
WHEN in.Created_Date < DATEADD(DAY, -90,  CONVERT(date, GETDATE())
THEN '90 Days'
END AS [Days Outstanding]

也可以简化为:

DECLARE @today date = GETDATE();
CASE
WHEN in.Created_Date < DATEADD(DAY, -365, @today)
THEN '365+ Days'
...

Answer 2

均为dbo。发票和发票。ar_receipts有一个Created_Date列

INNER JOIN dbo.invoices AS in
ON ar.Created_Date = in.Created_Date

您需要像上面那样完全限定Created_Date的每次使用。

例如，DATEDIFF(day, [Created_Date], Getdate()) as " Number_of_Days "需要指定哪个表的Created_Date。可能在。Created_Date，但只有你能肯定地回答这个问题。