mysql - 将父数据左连接到多个左连接子数据的简单方法

Question

是否有更简单的方法将父数据左连接到多个子左连接数据？

表结构:

Table1 {id, name, data1, data2, datax}
Table2 {id, table1_id, dataA, dataB, userid}
Table3 {id, table1_id, dataD, userid}
Table4 {id, table1_id, dataE, userid}
TableUsers {userid, username, name, email}

SQL 查询:

select Table1.*, Table2.*,Table3.*,Table4.*
from Table1 t1
left outer join Table2 t2 on t2.table1_id = t1.id
left outer join Table3 t3 on t3.table1_id = t1.id
left outer join Table4 t4 on t4.table1_id = t1.id

从这里开始，我想用实际用户名替换 userid 字段，每个表将有不同的 userid 数据，因此与 TableUsers 的简单左连接将不起作用，因为它只会影响设置为相等的表到。

我考虑过用子查询来做，但相信那会特别慢:

select Table1.*,(select TableUser.name from TableUser where Table1.userid = TableUser.userid) as T1Name,
 Table2.*,(select TableUser.name from TableUser where Table2.userid = TableUser.userid) as T2Name,
Table3.*, (select TableUser.name from TableUser where Table3.userid = TableUser.userid) as T3Name,
Table4.*,(select TableUser.name from TableUser where Table1.userid = TableUser.userid) as T4Name
from Table1 t1
left outer join Table2 t2 on t2.table1_id = t1.id
left outer join Table3 t3 on t3.table1_id = t1.id
left outer join Table4 t4 on t4.table1_id = t1.id

重点是从 SQL 数据库迁移到 MongoDB。也许我需要重新考虑整个查询...干杯，

芯片

Answer 1

执行此操作的“正常”方法是添加额外的连接:

select Table1.*, t1name.name, Table2.*, t2name.name, Table3.*, t3name.name,
       Table4.*, t4name.name
from Table1 t1
left outer join Table2 t2 on t2.table1_id = t1.id
left outer join Table3 t3 on t3.table1_id = t1.id
left outer join Table4 t4 on t4.table1_id = t1.id
left outer join TableUser t1name on t1.userid = t1name.userid
left outer join TableUser t2name on t2.userid = t2name.userid
left outer join TableUser t3name on t3.userid = t3name.userid
left outer join TableUser t4name on t4.userid = t4name.userid;

如果你在 TableUser(userid) 或 TableUser(userid, name) 上有一个索引，那么这两种方法的性能应该差不多。