在 SQL 中使用 2 个不明确的列名的查询错误

Question

我已经使用这个查询一段时间了，我遇到了困难。我是SQL的新手，我无法弄清楚为什么我会收到错误：

SELECT customer_number, first_name_initial, last_name,serve_address_1, serve_address_2, serve_city, serve_state, route_serve_zip_code, phone_number1, referral_code
FROM customer_master 
INNER JOIN route_detail 
ON m.customer_number=r.customer_number
WHERE (referral_code='american')

错误

Msg 209，级别 16，状态 1，第 1 行不明确的列名称"customer_number"。Msg 209，级别 16，状态 1，第 1 行不明确的列名称"phone_number1"。

我尝试按如下方式附加列，但也没有运气。任何帮助将不胜感激！

SELECT customer_number, first_name_initial, last_name,serve_address_1, serve_address_2, serve_city, serve_state, route_serve_zip_code, phone_number1, referral_code
FROM customer_master m
INNER JOIN route_detail r
ON m.customer_number=r.customer_number
WHERE (referral_code='american')

Answer 1

问题出在这一行

SELECT customer_number

您需要指定要从哪个表中获取customer_number，如下所示：

SELECT r.customer_number

或

SELECT m.customer_number

因此，您的最终查询将如下所示：

SELECT r.customer_number, --or m.customer_number
first_name_initial, last_name,serve_address_1, serve_address_2, serve_city, serve_state, route_serve_zip_code, phone_number1, referral_code
FROM customer_master 
INNER JOIN route_detail 
ON m.customer_number=r.customer_number
WHERE (referral_code='american')