mysql join 的同时可以筛选数据

看sql

select a.id as goods_id,a.name,a.store_id,a.salecount,a.logoimg,b.name as store_name,count(c.id) as mall_goods_count,c.type from sh_goods a LEFT JOIN sh_store b on a.store_id = b.id LEFT JOIN sh_mall_goods c on a.id = c.goods_id and c.mall_id = 9 where a.store_id in (select id from sh_store where user_id in (select id from sh_user where agent_id = 13 and status = 1) and status = 1) and a.status = 1 group by a.id;

这里面用了多个子查询，与join关联。

其中

LEFT JOIN sh_mall_goods c on a.id = c.goods_id and c.mall_id = 9

不仅有关联条件，还对sh_mall_goods表进行了筛选，只选出mall_id为9的数据，进行关联。

这很有意思。

下面是查询结果，有筛选与没筛选的区别。

 

 

转载于:https://www.cnblogs.com/jiqing9006/p/5078163.html