如何修改SQL语句，筛选recommend≥10的结果并按其降序排序？

需求说明 #

现有SQL语句返回recommend值≥1的结果，需调整为仅保留recommend值≥10的记录，同时维持按recommend字段降序排序的逻辑。

原代码 #

feedContent = SELECT (
            "*, count(*) AS recommend", 
            "post", 
            "$INNER_USER 
            INNER JOIN post_type ON post_id = type_post_id 
            WHERE post_status = 1 AND type_post = 'recommend' $category
            GROUP BY post_id 
            ORDER BY recommend DESC LIMIT 10

修改要点 #

• 由于recommend是通过count(*)生成的聚合别名，无法在WHERE子句中直接筛选，需使用HAVING子句（专门用于过滤分组后的聚合结果）
• 添加HAVING recommend >= 10来筛选符合条件的分组
• 保留原有的ORDER BY recommend DESC以满足降序排序需求

修改后的代码 #

feedContent = SELECT (
            "*, count(*) AS recommend", 
            "post", 
            "$INNER_USER 
            INNER JOIN post_type ON post_id = type_post_id 
            WHERE post_status = 1 AND type_post = 'recommend' $category
            GROUP BY post_id 
            HAVING recommend >= 10
            ORDER BY recommend DESC LIMIT 10

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