mysql - 如何: Counting albums and subalbums images and sum images to the main album

Question

我有两张 table

表:相册

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

表:相册图片

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