MySQL如何查询word_liked_id为0的记录并按点赞数降序排序
MySQL 词条按点赞数排序查询方案
可直接运行的查询语句
写法1(带点赞数返回,性能更优)
SELECT w1.*, COALESCE(like_count.total, 0) AS liked_num FROM words w1 LEFT JOIN ( SELECT word_liked_id, COUNT(*) AS total FROM words WHERE word_liked_id != 0 GROUP BY word_liked_id ) AS like_count ON w1.id = like_count.word_liked_id WHERE w1.word_liked_id = 0 ORDER BY liked_num DESC, w1.id ASC;
写法2(无需单独返回点赞数,语法更简洁)
SELECT w1.* FROM words w1 LEFT JOIN words w2 ON w1.id = w2.word_liked_id WHERE w1.word_liked_id = 0 GROUP BY w1.id ORDER BY COUNT(w2.id) DESC, w1.id ASC;
逻辑说明
- 内层子查询先统计所有有效点赞记录的对应词条和点赞总量,过滤新增词条的无效统计项
- 使用
LEFT JOIN关联常规词条(word_liked_id=0)和点赞统计结果,保证无点赞的词条也会正常返回 COALESCE函数将无点赞词条的空统计值转换为0,避免排序异常- 排序规则默认按点赞数降序,点赞数相同的词条按id升序排列,可根据业务需求调整排序字段
小数据量场景下写法2更易维护,大数据量场景优先使用写法1,先聚合再关联可以大幅降低关联运算的资源消耗
内容的提问来源于stack exchange,提问作者Naveh
相关产品推荐
相关产品推荐

