如何在无like_count列的likes表中找出数据库中获赞最多的照片？

核心思路 #

由于likes表通过(user_id, photo_id)联合主键记录每一次点赞行为，没有预存点赞数字段，因此需要按照片ID分组统计点赞次数，再关联其他表获取完整信息。

基础查询（取单张最高赞照片） #

如果只需要获取点赞数最高的单张照片（若有并列则取其中一张），可以用以下SQL：

SELECT 
    p.id AS photo_id,
    p.image_url,
    u.username AS uploader,
    COUNT(l.photo_id) AS like_count
FROM photos p
LEFT JOIN likes l ON p.id = l.photo_id
LEFT JOIN users u ON p.user_id = u.id
GROUP BY p.id, p.image_url, u.username
ORDER BY like_count DESC
LIMIT 1;

• 使用LEFT JOIN确保包含无点赞的照片（若仅需有赞照片，可替换为INNER JOIN）
• COUNT(l.photo_id)统计有效点赞数，因联合主键保证无重复点赞记录
• 按点赞数倒序排序后，LIMIT 1直接取排名第一的结果

处理并列最高赞的情况 #

如果存在多张照片点赞数相同且均为最高，可通过窗口函数取出所有符合条件的照片：

WITH photo_like_counts AS (
    SELECT 
        p.id AS photo_id,
        p.image_url,
        u.username AS uploader,
        COUNT(l.photo_id) AS like_count,
        RANK() OVER(ORDER BY COUNT(l.photo_id) DESC) AS like_rank
    FROM photos p
    LEFT JOIN likes l ON p.id = l.photo_id
    LEFT JOIN users u ON p.user_id = u.id
    GROUP BY p.id, p.image_url, u.username
)
SELECT photo_id, image_url, uploader, like_count
FROM photo_like_counts
WHERE like_rank = 1;

• 用CTE（公共表表达式）先计算每张照片的点赞数和排名
• RANK()函数会为点赞数相同的照片分配相同排名，确保所有最高赞照片都被取出

性能优化建议 #

为提升统计效率，可在likes表的photo_id字段创建索引：

CREATE INDEX idx_likes_photo_id ON likes(photo_id);

该索引会加速分组和关联操作，尤其是在数据量较大时效果明显。

内容的提问来源于stack exchange，提问作者Arun Jadhav