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的数据,进行关联。

这很有意思。

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

 

 

posted @ 2015-12-26 15:14  TBHacker  阅读(707)  评论(0编辑  收藏  举报