mysql left join复杂一点的查询方法示例

maxc1017

SELECT a.id,b.series_id,COUNT(b.id) cnt FROM
(SELECT id FROM lib_brand_series WHERE 2>1 AND brand_id=1 AND category_id=5 AND STATUS=1) a
LEFT JOIN
(SELECT id,series_id FROM lib_product_index a WHERE 2>1 AND small_category_id = 252 AND brand_id = 1) b
ON a.id = b.series_id GROUP BY a.id

执行计划：


优化后sql有一点点修改：

SELECT a.id,COUNT(b.id)
FROM lib_brand_series a LEFT JOIN
(SELECT * FROM lib_product_index WHERE small_category_id = 252 AND brand_id = 1)b
ON a.id = b.series_id
WHERE a.brand_id=1
AND a.category_id=5
AND a.STATUS=1
GROUP BY a.id

执行计划：

如图所示：