带 SUM 和 order 的 MySQL 查询

Question

我的查询有什么问题？有人可以帮我吗？

我收到此错误消息

"Unknown column 'wp_gdsr_data_article. (SUM(user_voters)+SUM(visitor_voters))' in 'order clause'"

但是“wp_gdsr_data_article”列存在

    SELECT *
    FROM `wp_posts`
    INNER JOIN wp_term_relationships ON wp_term_relationships.object_id = ID AND wp_term_relationships.term_taxonomy_id = 1
    INNER JOIN wp_gdsr_data_article ON post_id = ID
    WHERE `post_status` = 'publish'
    AND `post_type` = 'post'
    ORDER BY `wp_gdsr_data_article`.`(SUM(user_voters)+SUM(visitor_voters))` DESC
    LIMIT 1 , 30

Answer 1

您在 ORDER 子句中使用的表达式不是表的列。
因此，您不能对表达式的结果使用表标识符。

这是错误的。

ORDER BY `wp_gdsr_data_article`.`(SUM(user_voters)+SUM(visitor_voters))` DESC

将其更改为:

ORDER BY (SUM(user_voters)+SUM(visitor_voters)) DESC

而且您不能像这样在 ORDER BY 子句中直接使用任何聚合函数。
分别计算SUM...部分，然后在ORDER BY中使用。

SELECT * from (
    SELECT *, (SUM(user_voters)+SUM(visitor_voters)) total
    FROM `wp_posts`
    INNER JOIN wp_term_relationships 
            ON wp_term_relationships.object_id = ID 
               AND wp_term_relationships.term_taxonomy_id = 1
    INNER JOIN wp_gdsr_data_article 
            ON post_id = ID
    WHERE `post_status` = 'publish'
      AND `post_type` = 'post'
    LIMIT 1 , 30
) results
ORDER BY total

引用:How to ORDER BY a SUM() in MySQL?