mysql - 如何在MySql中为相同的表名创建两个JOIN？

SELECT table1.name, table2.wage, table2.bonus table3.shift, table3.endtime, table4.vacation
FROM table1
INNER JOIN table2 ON table1.userid = table2.userid
INNER JOIN table3 ON table2.userid = table3.userid
INNER JOIN table3 ON table2.endtime = table3.endtime
INNER JOIN table4 ON table4.userid = table4.userid
LEFT JOIN table5 ON table1.name = table5.position

这会引发错误：Not unique table/alias: 'table3'

我试过了

INNER JOIN table3 tbl3 ON table2.userid = table3.userid
    INNER JOIN tbl3 ON table2.endtime = tbl3.endtime

窗台不工作

最佳答案

给它一个唯一的别名：

SELECT table1.name, table2.wage, table2.bonus t3a.shift, t3b.endtime, table4.vacation
FROM table1
INNER JOIN table2 ON table1.userid = table2.userid
INNER JOIN table3 t3a ON table2.userid = t3a.userid
INNER JOIN table3 t3b ON table2.endtime = t3b.endtime
INNER JOIN table4 ON table4.userid = table4.userid
LEFT JOIN table5 ON table1.name = table5.position;

实际上，它使整洁的代码为每个表赋予别名：

SELECT
    t1.name,
    t2.wage,
    t2.bonus,
    t3a.shift,
    t3b.endtime,
    t4.vacation
FROM table1 t1
INNER JOIN table2 t2 ON t1.userid = t2.userid
INNER JOIN table3 t3a ON t2.userid = t3a.userid
INNER JOIN table3 t3b ON t2.endtime = t3b.endtime
INNER JOIN table4 t4 ON t4.userid = t1.userid
LEFT JOIN table5 t5 ON t1.name = t5.position;

看来您在table4的联接中有错误，并且selects中缺少逗号-我已在最后一个版本中对其进行了更正。

关于mysql - 如何在MySql中为相同的表名创建两个JOIN？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6897660/