关于MySql 查询语句的问题 LEFT JOIN

Question

我有一个结果集，然后我想要另一张表对着我的查询的结果有的显示内容，没有的显示空。
SELECT E.classname,E.classid,S.title,S.kjdata FROM phome_ecms_ssc S, phome_enewsclass E,(SELECT id FROM phome_ecms_ssc where classid = 26 and kjdate > UNIX_TIMESTAMP(NOW()) ORDER BY kjdate LIMIT 1) B where S.classid = 26 and S.classid = E.classid and S.id <= B.id ORDER BY S.id DESC LIMIT 10
这一段是我的的结果
phome_ecms_yuce
这张是我要关联的表

我真的没有用过 JOIN 最终我写的是

SELECT E.classname,E.classid,S.title,S.kjdata FROM phome_ecms_ssc S, phome_enewsclass E,(SELECT id FROM phome_ecms_ssc where classid = 26 and kjdate > UNIX_TIMESTAMP(NOW()) ORDER BY kjdate LIMIT 1) B where S.classid = 26 and S.classid = E.classid and S.id <= B.id ORDER BY S.id DESC LIMIT 10
LEFT JOIN phome_ecms_yuce Y ON S.classid = Y.wanfaid and S.title = Y.qihao and Y.jihuaid = 1

这个写法是错误的，求大神指点

Answer 1

left join是不能放在where后面的，如果是想要join前面生成的结果集的话，需要将生成结果集再join

select * from (SELECT E.classname,E.classid,S.title,S.kjdata FROM phome_ecms_ssc S, phome_enewsclass E,(SELECT id FROM phome_ecms_ssc where classid = 26 and kjdate > UNIX_TIMESTAMP(NOW()) ORDER BY kjdate LIMIT 1) B where S.classid = 26 and S.classid = E.classid and S.id <= B.id ORDER BY S.id DESC LIMIT 10 ) ls left join phome_ecms_yuce Y ON Y.wanfaid=ls.classid and Y.qihao=ls.title and Y.jihuaid = 1

Answer 2

order by 不能再left join 之前的，所以会报错

Answer 3

SELECT E.classname,E.classid,S.title,S.kjdata
FROM phome_ecms_ssc S, phome_enewsclass E,
(SELECT id FROM phome_ecms_ssc where classid = 26 and kjdate > UNIX_TIMESTAMP(NOW()) ORDER BY kjdate LIMIT 1) B
LEFT JOIN phome_ecms_yuce Y ON S.classid = Y.wanfaid and S.title = Y.qihao and Y.jihuaid = 1
where S.classid = 26 and S.classid = E.classid and S.id <= B.id
ORDER BY S.id DESC LIMIT 10

Answer 4

把 ORDER BY S.id DESC LIMIT 10 放到最后面

Answer 5

要通过left join先生成虚拟表，然后在虚拟表中执行where筛选，所以要把where放在left join之后。

Answer 6

select * from (select * from phome_ecms_yuce where jihuaid = 1) Y left join
(SELECT E.classname,E.classid,S.title,S.kjdata FROM phome_ecms_ssc S, phome_enewsclass E,(SELECT id FROM phome_ecms_ssc where classid = 26 and kjdate > UNIX_TIMESTAMP(NOW()) ORDER BY kjdate LIMIT 1) B where S.classid = 26 and S.classid = E.classid and S.id <= B.id ORDER BY S.id DESC LIMIT 10 )as s
ON S.classid = Y.wanfaid and S.title = Y.qihao

Answer 7

order by 不能再left join 之前的，所以会报错

Answer 8

left join是连接查询的，是主表查询的附加查询，order by是对整个查询结果进行处理的，所以order by的作用域是针对整个查询语句，要放在sql查询的最后

Answer 9

join 是内连接, 就是2张表都存在才显示
left join 是左连接,以左边这个表为主,左边的存在记录就存在
SELECT E.classname,E.classid,S.title,S.kjdata
FROM phome_ecms_ssc S, phome_enewsclass E
,(SELECT id FROM phome_ecms_ssc where classid = 26 and kjdate > UNIX_TIMESTAMP(NOW())) B
where S.classid = 26 and S.classid = E.classid and S.id <= B.id
LEFT JOIN phome_ecms_yuce Y ON S.classid = Y.wanfaid and S.title = Y.qihao and Y.jihuaid = 1
ORDER BY S.id DESC LIMIT 10

Answer 10

order by 绝对是不能再left join 之前的

Answer 11

order by是语句的之后，这个可以理解性记忆。如果想系统的学习或回忆SQL使用，请参看:http://blog.csdn.net/thinking_fioa/article/details/78265745

Answer 12

用法：left join 写在where前面，
理解：左外链接，左表(Dave)的记录将会全部表示出来,而右表(BL)只会显示符合搜索条件的记录。BL表记录不足的地方均为NULL.

Answer 13

试试这个
(SELECT E.classname,E.classid,S.title,S.kjdata
FROM phome_ecms_ssc S, phome_enewsclass E,
(
SELECT id
FROM phome_ecms_ssc
where classid = 26
and kjdate > UNIX_TIMESTAMP(NOW())
ORDER BY kjdate LIMIT 1 ) B
where S.classid = 26
and S.classid = E.classid
and S.id <= B.id ) A
LEFT JOIN phome_ecms_yuce Y
ON A.classid = Y.wanfaid
and A.title = Y.qihao
and Y.jihuaid = 1
ORDER BY A.id DESC
LIMIT 10