使用 MySQL 连接三个表

小开

使用 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

小开

最佳答案

简单使用:

select s.name "Student", c.name "Course"
from student s, bridge b, course c
where b.sid = s.sid and b.cid = c.cid

小开

用来标准化形态

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

小开

用这个:

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);

小开

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

小开

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;

您可以像上面的示例那样连接多个表。

小开

要联接两个以上表的查询:

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

小开

别这样加入我们。这是一个非常非常糟糕的练习! ! ！它会降低获取大量数据的性能。例如，如果每个表中有100行，数据库服务器必须提取 100x100x100 = 1000000次。它必须为 1 million取值几次。为了克服这个问题，加入前两个能够获取最小可能匹配结果的表(这取决于您的数据库模式)。在 Subquery 中使用这个结果，然后将其与第三个表连接并获取它。对于第一个 join-> 100x100= 10000次，假设我们得到5个匹配结果。然后我们将第三个表与结果联接起来—— > 5x100 = 500. Total get = 10000+500 = 10500 times only。就这样，演出开始了! ! ！

小开

只是在之前的答案中添加一个点，在 MySQL 中我们可以使用

table_factor syntax

或者

joined_table syntax

Mysql 文档

Table _ factor 示例

SELECT prd.name, b.name
FROM products prd, buyers b

联合表示例

SELECT prd.name, b.name
FROM products prd
left join buyers b on b.bid = prd.bid;

供参考: 请忽略这样一个事实，即联接表示例中的左联接没有多大意义(实际上，我们会使用某种联接表将买方链接到产品表，而不是在产品表中保存 buyerID)。

小开

Query for three table join and limit set


SELECT * FROM (SELECT t1.follower_userid, t2.*, t3.login_thumb, t3.login_name,
t3.bio, t3.account_status, t3.gender
FROM videos t2
LEFT JOIN follower t1
ON t1.follower_userid = t2.user_id
LEFT JOIN videos_user t3
ON t1.follower_userid  = t3.login_userid
WHERE t1.following_userid='$userid'
LIMIT $startpoint , $limit) AS ID
ORDER BY ID DESC

小开

使用三个表进行连接查询，我们需要来自同一列的两个值，我们为连接中的每个表设置了别名。相同的表名也声明为不同的名称。

const sql = `select p.ID,p.purchaseamount,urs.name as
buyername,pd.productname,
pd.amount,urs1.name as sellername
from  purchases p
left join products pd on p.productid=pd.ID
left join users urs on p.userid=urs.ID
left join users urs1 on  pd.userid=urs1.ID`