如何编写SQL实现查询优先展示收藏记录，后续展示最新未收藏记录

问题根源 #

你当前查询仅返回收藏奖项的核心原因是对awards_user_favorite表使用了INNER JOIN，内连接的特性是只有关联表匹配到对应记录时才会返回行，因此没有收藏记录的奖项会被直接过滤。

修正后的SQL语句 #

SELECT
    a.award_name,
    a.award_image_path,
    a.award_id,
    au.user_id,
    auf.awards_user_favorite_id,
    -- 可选新增字段：直接返回收藏状态，方便业务层判断
    CASE WHEN auf.awards_user_favorite_id IS NOT NULL THEN 1 ELSE 0 END AS is_favorite
FROM awards_user AS au
INNER JOIN users AS u ON au.user_id = u.uid
INNER JOIN awards AS a ON au.award_id = a.award_id
-- 改为左连接，保留该用户所有获得的奖项
LEFT JOIN awards_user_favorite AS auf 
    ON auf.award_id = a.award_id 
    AND auf.user_id = au.user_id -- 关联当前用户的收藏记录，避免匹配到其他用户的收藏
WHERE au.user_id = 1
ORDER BY
    -- 第一优先级：收藏的奖项置顶
    CASE WHEN auf.awards_user_favorite_id IS NOT NULL THEN 0 ELSE 1 END ASC,
    -- 第二优先级：收藏的奖项按收藏时间排序（主键升序等价于早收藏的在前，可按需调整为降序）
    auf.awards_user_favorite_id ASC,
    -- 第三优先级：未收藏的奖项按最新获得排序（award_user_id越大越新，用降序）
    au.award_user_id DESC
LIMIT 10

关键修改说明 #

1. 连接类型调整：将收藏表的内连接改为左连接，确保所有该用户获得的奖项都不会被过滤
2. 关联条件补充：左连接时增加用户ID匹配规则，避免出现A用户的奖项关联到B用户收藏的脏数据
3. 条件位置注意：收藏表的用户ID匹配必须写在LEFT JOIN的ON子句中，不能移到WHERE里，否则未收藏的记录会因为auf.user_id为NULL被过滤，回到之前仅返回收藏奖项的问题
4. 排序逻辑优化：新增收藏状态判断作为最高排序优先级，保证收藏的奖项全部排在未收藏的前面

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