mysql - #1066 - 不是唯一的表/别名 : 'fx_users'

Question

这里我遇到这样的错误，#1066 - 不是唯一的表/别名:'fx_users'。

我卡在哪儿了？

这就是我的查询:

SELECT from_date
    , to_date
    , fx_users.username
    , fx_leave_type.NAME
    , STATUS
FROM fx_leave_assign
INNER JOIN fx_leave_type
    ON fx_leave_type.id = fx_leave_assign.leavetype_id
INNER JOIN fx_users
    ON fx_users.id = fx_leave_assign.user_id
INNER JOIN fx_users_attendance
    ON fx_users_attendance.user_id = fx_users.id
INNER JOIN `fx_account_details`
    ON fx_account_details.user_id = fx_users_attendance.user_id
INNER JOIN `fx_departments`
    ON fx_departments.deptid = fx_account_details.department
INNER JOIN `fx_users`
    ON fx_users.id = fx_users_attendance.user_id
WHERE fx_departments.deptid IN (1)
    AND fx_users.id IN (5)
    AND DATE (fx_users_attendance.clock_in) >= "2015-09-01"
    AND DATE (fx_users_attendance.clock_in) <= "2015-09-30"
    AND fx_leave_assign.leavetype_id = "1";

Answer 1

原因很简单，在您的查询中您加入了两次fx_users。您需要为它们添加别名。

SELECT from_date,
       to_date,
       fu1.username,
       fx_leave_type.name,
       STATUS
FROM fx_leave_assign
JOIN fx_leave_type ON fx_leave_type.id = fx_leave_assign.leavetype_id
JOIN fx_users AS fu1 ON fu1.id = fx_leave_assign.user_id
JOIN fx_users_attendance ON fx_users_attendance.user_id=fu1.id
JOIN `fx_account_details` ON fx_account_details.user_id = fx_users_attendance.user_id
JOIN `fx_departments` ON fx_departments.deptid = fx_account_details.department
JOIN `fx_users` AS fu2 ON fu2.id = fx_users_attendance.user_id
WHERE fx_departments.deptid IN(1)
  AND fu1.id IN(5)
  AND date(fx_users_attendance.clock_in) >= "2015-09-01"
  AND date(fx_users_attendance.clock_in) <= "2015-09-30"
  AND fx_leave_assign.leavetype_id="1"

您还应该为 from_date、to_date 和 STATUS 列别名。有多个表，您可以获得不明确的名称。