其实,我这里写的是一个相册,获取所有相册分类下的,如果是只获取文章标题这比较简单,一个left join 就可以完成,现在主要是要获取数量,那就必须要进行表的派生,SQL语句如下:

SELECT photo_xiangce.taozhuang_name, photo_xiangce.id, photo_img.img_small as fengmian, c.NUM AS c_num
FROM photo_xiangce
LEFT JOIN photo_img ON photo_img.xiangce_id = photo_xiangce.id
LEFT JOIN (
SELECT COUNT( * ) AS NUM, photo_img. xiangce_id
FROM photo_img
GROUP BY xiangce_id
) AS c ON c.xiangce_id = photo_xiangce.id