如何:将相册和子相册图像和和图像计数到主相册

Question

我有两张桌子

表:相册

album_id  name       parent_album_id
------------------------------------
69        ....       NULL
71        ....       69
72        ....       69
70        ....       NULL
75        ....       NULL
74        ....       NULL
73        ....       NULL

表: album_image

album_id      image_id
----------------------
69             256
69             216
69             89
71             896
70             85

我希望统计相册中的所有图像，如果某些相册有子相册，那么将该图像和主相册相加。

就像这样：

album_id        total_images
----------------------------
69              4
70              1
73              0
74              0
75              0

请注意，我没有显示子相册(id: 71和id: 72)

这是我所做的查询，但我没有显示正确的总图像

select a.album_id, count(*)
from album a
left join album a2 on a.album_id = a2.parent_album_id
join album_image ai on ai.image_id = IFNULL(a.album_id, a2.album_id)
group by a.album_id

Answer 1

这是你的解决方案-

SELECT IFNULL(a.parent_album_id,a.album_id) AS album_id,sum(result.image_count) as total_images
FROM album a
LEFT JOIN (SELECT album_id,count(image_id) AS image_count FROM album_image GROUP BY album_id) result
ON result.album_id = a.album_id
GROUP BY album_id