mysql--使用left join条件查询时加where条件的问题

场景：为一个活动添加指导文件，每一个活动会对应多种指导文件类型，进入每一个活动的设置指导文件页面后所呈现的指导文件类型相同，查询时，使用指导文件类型表LEFT JOIN指导文件表，要求指导文件类型全部列出，已经填写的指导文件项在其后标出，而指导文件表只有一张，所有活动、指导文件类型的数据都在这里，要通过不同的活动进行筛选，一开始的sql语句是这样的：

SELECT `zxb_guidebooks_type`.*, `zxb_leading_guidebooks`.*, `zxb_guidebooks_type`.`id` AS `type_id`, `zxb_leading_guidebooks`.`id` AS `guide_id` FROM `zxb_guidebooks_type` LEFT JOIN `zxb_leading_guidebooks` ON zxb_leading_guidebooks.type_id=zxb_guidebooks_type.id WHERE (zxb_guidebooks_type.status=1) AND (zxb_guidebooks_type.pid!=0) AND (zxb_leading_guidebooks.leading_id=2) ORDER BY `p_order` LIMIT 20

查询后发现，返回的结果为EMPTY SET，查阅资料后原来，数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户，where条件会在临时表生成好后，再对临时表的结果进行过滤，于是就出现了EMPTY SET的情况，LEFT JOIN在加上WHERE条件后就会转变为join，想要解决这个问题有两种办法

一、最简单的方法，可以把该WHERE条件写在ON之后

SELECT `zxb_guidebooks_type`.*, `zxb_leading_guidebooks`.*, `zxb_guidebooks_type`.`id` AS `type_id`, `zxb_leading_guidebooks`.`id` AS `guide_id` FROM `zxb_guidebooks_type` LEFT JOIN `zxb_leading_guidebooks` ON zxb_leading_guidebooks.type_id=zxb_guidebooks_type.id and zxb_leading_guidebooks.leading_id=2 WHERE (zxb_guidebooks_type.status=1) AND (zxb_guidebooks_type.pid!=0) ORDER BY `p_order` LIMIT 20

 二、将有where条件的LEFT JOIN作为一个临时表，然后用指导文件类型表，再LEFT JOIN这个临时表，这样无论where怎么变也不会影响最终结果了

SELECT a.*,tmp.* FROM a LEFT JOIN (SELECT a.*,b.* FROM a LEFT JOIN b ON a.b_id=b.id WHERE b.dd=1 AND a.status=1) as tmp WHERE a.status=1;

 

转载于:https://www.cnblogs.com/peilanluo/p/8426763.html