我有两张表:相册和照片
相册
id | 名称 | user_id |
---|---|---|
1 | 2022年新年 | 2|
2 | 生日派对 | 2|
3 | 婚礼 | 2 |
这就是我用ms-sql编写它的方式。您可能需要针对postgresql对其进行调整。
SELECT a.id, a.name, a.user_id, count(*) as photo_count
FROM ALBUMS a
INNER JOIN PHOTOS p on a.id = p.id
GROUP BY a.id, a.name, a.user_id
我找到了答案,那就是
select albums.id, albums.name, albums.user_id,(select count(*) from photos where photos.album_id = albums.id) as photo_count from albums where user_id = 2
好的,根据@Sonyck的建议,解决方案将是
SELECT albums.id, albums.name, albums.user_id, count(*) as photo_count
FROM albums
JOIN photos ON (photos.album_id = albums.id)
WHERE user_id = 2
GROUP BY albums.id, albums.name, albums.user_id