使用 MySQL 连接三个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3709560/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining three tables using MySQL
提问by PHP Ferrari
I have three tables named
我有三个表命名
**Student Table**
-------------
id name
-------------
1 ali
2 ahmed
3 john
4 king
**Course Table**
-------------
id name
-------------
1 physic
2 maths
3 computer
4 chemistry
**Bridge**
-------------
sid cid
-------------
1 1
1 2
1 3
1 4
2 1
2 2
3 3
3 4
4 1
4 2
Now to show the student name with the course name which he had studied like,
现在用他学习的课程名称显示学生姓名，
**Result**
---------------------------
Student Course
---------------------------
ahmed physic
ahmed maths
ahmed computer
ahmed chemistry
ali physic
ali maths
john computer
john chemistry
king physic
king maths
I build following query
我构建以下查询
select s.name as Student, c.name as Course from student s, course c join bridge b on c.id = b.cid order by s.name
But it does not return the required result...
但它没有返回所需的结果......
And what would be for normalized form, if I want to find who is manager over other:
如果我想找到谁是经理而不是其他人，那么规范化形式会是什么：
**employee**
-------------------
id name
-------------------
1 ali
2 king
3 mak
4 sam
5 jon
**manage**
--------------
mid eid
--------------
1 2
1 3
3 4
4 5
And wants to get this result:
并且想要得到这样的结果：
**result**
--------------------
Manager Staff
--------------------
ali king
ali mak
mak sam
sam jon
回答by RedFilter
Use ANSI syntax and it will be a lot more clear how you are joining the tables:
使用 ANSI 语法，您将如何加入表会更加清楚：
SELECT s.name as Student, c.name as Course
FROM student s
INNER JOIN bridge b ON s.id = b.sid
INNER JOIN course c ON b.cid = c.id
ORDER BY s.name
回答by raisyn
Simply use:
只需使用：
select s.name "Student", c.name "Course"
from student s, bridge b, course c
where b.sid = s.sid and b.cid = c.cid
回答by PHP Ferrari
For normalize form
对于规范化形式
select e1.name as 'Manager', e2.name as 'Staff'
from employee e1
left join manage m on m.mid = e1.id
left join employee e2 on m.eid = e2.id
回答by Ali Umair
SELECT *
FROM user u
JOIN user_clockits uc ON u.user_id=uc.user_id
JOIN clockits cl ON cl.clockits_id=uc.clockits_id
WHERE user_id = 158
回答by sameerNAT
SELECT
employees.id,
CONCAT(employees.f_name," ",employees.l_name) AS 'Full Name', genders.gender_name AS 'Sex',
depts.dept_name AS 'Team Name',
pay_grades.pay_grade_name AS 'Band',
designations.designation_name AS 'Role'
FROM employees
LEFT JOIN genders ON employees.gender_id = genders.id
LEFT JOIN depts ON employees.dept_id = depts.id
LEFT JOIN pay_grades ON employees.pay_grade_id = pay_grades.id
LEFT JOIN designations ON employees.designation_id = designations.id
ORDER BY employees.id;
You can JOIN multiple TABLES like this example above.
您可以像上面的示例一样加入多个 TABLES。
回答by Manish
Query to join more than two tables:
查询加入两个以上的表：
SELECT ops.field_id, ops.option_id, ops.label
FROM engine4_user_fields_maps AS map
JOIN engine4_user_fields_meta AS meta ON map.`child_id` = meta.field_id
JOIN engine4_user_fields_options AS ops ON map.child_id = ops.field_id
WHERE map.option_id =39 AND meta.type LIKE 'outcomeresult' LIMIT 0 , 30
回答by Dracula Oppa
Don't join like that. It's a really really bad practice!!! It will slow down the performance in fetching with massive data. For example, if there were 100 rows in each tables, database server have to fetch 100x100x100 = 1000000times. It had to fetch for 1 milliontimes. To overcome that problem, join the first two table that can fetch result in minimum possible matching(It's up to your database schema). Use that result in Subquery and then join it with the third table and fetch it. For the very first join --> 100x100= 10000times and suppose we get 5 matching result. And then we join the third table with the result --> 5x100 = 500.Total fetch = 10000+500 = 10200times only. And thus, the performance went up!!!
不要那样加入。这真的是一个非常糟糕的做法！！！它会降低获取海量数据的性能。例如，如果每个表中有 100 行，则数据库服务器必须提取100x100x100 = 1000000次数。它不得不1 million多次取货。为了克服这个问题，加入前两个可以获取最小匹配结果的表（这取决于你的数据库模式）。在子查询中使用该结果，然后将其与第三个表连接并获取它。对于第一次加入 -->100x100= 10000次，假设我们得到 5 个匹配结果。然后我们将结果与第三个表连接起来 --> 5x100 = 500.Total fetch = 10000+500 = 10200times only。因此，性能上升了！！！
回答by user3477784
Use this:
用这个：
SELECT s.name AS Student, c.name AS Course
FROM student s
LEFT JOIN (bridge b CROSS JOIN course c)
ON (s.id = b.sid AND b.cid = c.id);
