SQL SSMS执行多表连接查询提示Created_Date列名不明确报错

报错原因 #

• 核心原因是列名歧义：你关联的ar_receipts（别名ar）和invoices（别名in）两张表都存在Created_Date字段，你在DATEDIFF函数中引用Created_Date时没有指定表别名，SQL引擎无法判断该取哪张表的字段，因此抛出Msg 209错误。
• 额外隐藏问题：你给residents表定义的别名是rs，但SELECT语句中引用用户姓名时用了不存在的别名rsdt，修复歧义问题后该问题也会触发报错，需要同步调整。

修复方案 #

1. 给所有未加别名的Created_Date引用加上表别名，根据业务逻辑这里取in表（发票表）的创建时间即可；
2. 修正居民表的别名引用错误，把rsdt改成rs；
3. 同步修正WHERE条件中> =的多余空格避免语法报错。

修正后的完整脚本如下：

SELECT 
 rs.Resident_ID, 
 [UserName] = rs.First_Name + ' ' + rs.Last_Name,
 in.Invoice_Amount,
 in.Due_Date as due,
 in.Created_Date,
 DATEDIFF(day, in.Created_Date, Getdate()) as "Number_of_Days",
 in.Paid_Date as paid,
 in.Description,  
 ar.Payment_Type, 
 ar.Receipt_Descriptions,

 CASE 
     WHEN DATEDIFF(day, in.Created_Date, Getdate()) Between 90 AND 119 Then '90 days'
     WHEN DATEDIFF(day, in.Created_Date, Getdate()) Between 120 AND 179 Then '120 days'
     WHEN DATEDIFF(day, in.Created_Date, Getdate()) Between 180 AND 364 Then '180 days'
     WHEN DATEDIFF(day, in.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

内容的提问来源于stack exchange，提问作者Sweetcorn