我一直在处理一个相当长的查询，它抛出错误"#1052 - 字段列表中的列'lat'不明确"。 我已将其分成多个部分，每个部分似乎都可以正常工作，但是当我立即运行它时，我会收到此错误。 下面是查询：
SELECT lesson_requests_global_2.student_name,
(3959 * ACOS(COS(RADIANS(30.096595)) * COS(RADIANS(lat)) * COS(RADIANS(lng) - RADIANS(- 81.718983)) + SIN(RADIANS(30.096595)) * SIN(RADIANS(lat)))) AS distance,
lesson_requests_vendor.user_purchased
FROM lesson_requests_global_2
INNER JOIN
( SELECT student_name,
MAX(request_date) AS max_request_date
FROM lesson_requests_global_2
WHERE ( 3959 * ACOS(COS(RADIANS(30.096595)) * COS(RADIANS(lat)) * COS(RADIANS(lng) - RADIANS(- 81.718983)) + SIN(RADIANS(30.096595)) * SIN(RADIANS(lat))) ) < 30
GROUP BY student_name ) AS recent_student_lesson_request ON lesson_requests_global_2.student_name = recent_student_lesson_request.student_name
AND lesson_requests_global_2.request_date = recent_student_lesson_request.max_request_date
LEFT JOIN lesson_requests_vendor ON v.user_purchased = lesson_requests_global_2.student_name
WHERE lesson_requests_vendor.user_purchased <> 'bob jones'
AND distance < 30
ORDER BY distance LIMIT 0 , 20
请注意，长COS/RADIAS的东西看起来很复杂，但它是找到一英里半径的距离。 我认为不知何故，它认为这些公式中的"lat"以某种方式在列列表中？
提前感谢您的帮助！
非常简单。
您从中选择的同一表上联接，因此您将拥有两列具有相同名称的列。如果不在字段名称之前放置"表名"，这将产生 sql 错误。
你可以做这样的事情：
SELECT .... FROM lesson_requests_global_2 request
INNER JOIN
( SELECT ..... FROM lesson_request_globals_2 .....)
....
WHERE ....
并重命名request.lat中出现的lat
request现在是表名的别名："几乎"是您选择的第一个。
听起来lesson_requests_global_2和lesson_requests_vendor都有一个名为"lat"的列。您需要指定要从哪个表中查询它：
SELECT lesson_requests_global_2.student_name,
(3959 * ACOS(COS(RADIANS(30.096595)) * COS(RADIANS(lesson_requests_global_2.lat)) * COS(RADIANS(lng) - RADIANS(- 81.718983)) + SIN(RADIANS(30.096595)) * SIN(RADIANS(lat)))) AS distance,
lesson_requests_vendor.user_purchased
FROM lesson_requests_global_2
INNER JOIN
( SELECT student_name,
MAX(request_date) AS max_request_date
FROM lesson_requests_global_2
WHERE ( 3959 * ACOS(COS(RADIANS(30.096595)) * COS(RADIANS(lesson_requests_global_2.lat)) * COS(RADIANS(lng) - RADIANS(- 81.718983)) + SIN(RADIANS(30.096595)) * SIN(RADIANS(lesson_requests_global_2.lat))) ) < 30
GROUP BY student_name ) AS recent_student_lesson_request ON lesson_requests_global_2.student_name = recent_student_lesson_request.student_name
AND lesson_requests_global_2.request_date = recent_student_lesson_request.max_request_date
LEFT JOIN lesson_requests_vendor ON v.user_purchased = lesson_requests_global_2.student_name
WHERE lesson_requests_vendor.user_purchased <> 'bob jones'
AND distance < 30
ORDER BY distance LIMIT 0 , 20