SELECT a.*,t1.photo_num,t3.fensi_num FROM account a
LEFT JOIN
( SELECT COUNT(p.user_id) photo_num, p.user_id FROM account a
INNER JOIN photo_table p ON p.user_id=a.user_id
WHERE 1 GROUP BY a.user_id ) AS t1
ON a.`user_id`=t1.user_id
LEFT JOIN
( SELECT COUNT(fs.gz_id) AS fensi_num,a.user_id FROM account a
INNER JOIN user_attention fs ON fs.user_id=a.user_id
WHERE 1 GROUP BY a.user_id
) AS t3
ON a.`user_id`=t3.user_id
WHERE 1 AND user_type=1 GROUP BY a.user_id ORDER BY photo_num ASC LIMIT 0,15
贴出执行计划和相关表及涉及字段的说明:
(表一)photo_table:(相册表)
photo_id int
user_id int
images_url varchar
(表二)user_attention:(关注表)
gz_id int
user_id int
guanzhu_id
(表三)
account:(用户表)
user_id int
user_name varchar
效果是:
统计 每个用户 有多少张照片,和多少个关注人。
请大神帮帮忙~~~