sql -连接2个表10次

Question

我有两个表，结构如下：

People
----------
>ID, Name, Surname

Projects
----------
>ID, Assistant#1,  Assistant#2,  Assistant#3,  Assistant#4, 
> Assistant#5,  Assistant#6,  Assistant#7,  Assistant#8,  Assistant#9, 
> Assistant#10, lot of other columns..... (every assistant column
> contains an integer, the people ID)

现在，我想要查询我的数据库并从Project表中获得一行(假设使用ID=3的行)，其中包含Project表的所有列，特别是将每个助理替换为其正确的姓名和姓氏。我已经设法做到了这一点(通过大量的连接)，但凭借我的少量sql经验，我的代码看起来非常笨重，我相信有更好的方法来做到这一点。

提前谢谢。

Answer 1

因为您将(可能)助理的数量设置为10，这就要求您必须编写代码来容纳这10个助理。这可以通过以下几种方式完成：(尚未测试)

select * from projects proj
left join people p1 on proj.assistant1 = p1.peopleid
left join people p2 on proj.assistant2 = p2.peopleid
left join people p3 on proj.assistant3 = p3.peopleid
left join people p4 on proj.assistant4 = p4.peopleid
left join people p5 on proj.assistant5 = p5.peopleid
left join people p6 on proj.assistant6 = p6.peopleid
left join people p7 on proj.assistant7 = p7.peopleid
left join people p8 on proj.assistant8 = p8.peopleid
left join people p9 on proj.assistant9 = p9.peopleid
left join people p10 on proj.assistant10 = p10.peopleid

否则你可以用一些小把戏

select proj.projectID, 
(select * from people where peopleID = proj.assistant1),
(select * from people where peopleID = proj.assistant2),
(select * from people where peopleID = proj.assistant3),
(select * from people where peopleID = proj.assistant4),
(select * from people where peopleID = proj.assistant5),
(select * from people where peopleID = proj.assistant6),
(select * from people where peopleID = proj.assistant7),
(select * from people where peopleID = proj.assistant8),
(select * from people where peopleID = proj.assistant9),
(select * from people where peopleID = proj.assistant10)
from projects proj

如果可能，最好重新构造数据表，并将单个助手映射到单个projectID：

PeopleID, Name, Surname

ProjectID, PeopleID

因此，您可以只执行一个内部连接，并为每个助手返回一行：

select * from projects proj 
inner join people p 
on p.peopleID = proj.peopleid
where proj.projectID = PROJECTID

Answer 2

SELECT p.Name, p.Surname
FROM People p
CROSS JOIN Project j ON (p.PeopleID = j.Assistant1 OR
p.PeopleID = j.Assistant2 OR
p.PeopleID = j.Assistant3 OR
.. etc)
AND j.ProjectID = 3

您的性能将非常糟糕，但这是您为拥有一个设计糟糕的数据库而付出的代价。更好的解决方案是通过分解的方式映射项目和人员：

CREATE TABLE proj_people
People_ID INT
Project_ID INT

Answer 3

你的结构我不清楚。我建议你为项目创建一个离合器。

表：

Peoples people_id，people_name，people_surname

Projects project_id，project_name

Clutch project_id，people_id