select a.*,c.f from (select * from a inner join b on a.a = b.a) as a inner join c on a.d = c.d
select a.*,c.f from a inner join b on a.a = b.a inner join b.d = b.d
两个都可以,第一个更容易理解,括号里的部分是查询出查询表A的数据和当表A的a=表B的a的数据,把这个结果集,再inner join c on a.d = c.d
查询出表B的d=表C的d 所对应的f的值。
三个表左连接的sql语句
select
intro_web_page.id,
web_id,
web_name,
template_id ,
template_name,
template_param,
intro_web_page.sort_order
from intro_web_page left join intro_web on (web_id=intro_web.id)
left join intro_template on (intro_web_page.template_id=intro_template.id)
注:这里主要是看,当两个表和某一个表都有联系的时候,左连接left join on要怎么写。
更新语句
UPDATE issue INNER JOIN user ON `issue`.insert_uid and `user`.id INNER JOIN
(SELECT issue.id as id,issue.insert_uid as uid,`user`.school_id as school_id,school.area as area1 from issue,user,school
where issue.insert_uid=`user`.id and `user`.school_id=school.id) AS T1
ON issue.insert_uid=T1.uid
SET issue.area=T1.area1
select a.*,c.f from (select * from a inner join b on a.a = b.a) as a inner join c on a.d = c.d
select a.*,c.f from a inner join b on a.a = b.a inner join b.d = b.d
两个都可以,第一个更容易理解 括号里的部分 是查询出 查询表A的数据和当表A的a=表B的a的数据,把这个结果集 再inner join c on a.d = c.d
查询出表B的d=表C的d 所对应的f的值.
最简单的方法就是普通连结:
select * from 表A,表B,表C
where 表A.a = 表B.a and 表B.d = 表C.d
还可以使用内连结,外连结,不过这里用这个就可以了
这个我自己运行过,可以用:student表,score表,course表。案例如下:
SELECT S.SNAME,C.CNAME,E.DEGREE FROM STUDENT S JOIN SCORE E ON S.SNO=E.SNO JOIN COURSE C ON C.CNO=E.CNO