mysql - SQL添加列

我对两列求和的基本操作有问题。这很简单，但不起作用。
我得到结果5+5=8，3+7=7
这是一个查询：

select
    `wp_posts`.ID ,
    (select count(*) from `co_likes`
     where `wp_posts`.`ID` = `co_likes`.`id_post`
       and `co_likes`.`deleted_at` is null) as `like_count`,
    (select count(*) from `wp_comments`
     where `wp_posts`.`ID` = `wp_comments`.`comment_post_ID`) as `comment_count` ,
    (`comment_count`+`like_count`) as 'total_sum'
from
    `wp_posts`
where
    `post_type` in ('post', 'co_post')
    and `post_status` = 'publish'
order by
    (comment_count+like_count) desc;

结果是：

知道怎么回事吗？

最佳答案

不能在定义列别名的位置使用它们。在您的情况下，最好的可能是子查询：

select p.*, (`comment_count`+`like_count`) as total_sum
from (select `wp_posts`.ID ,
             (select count(*) from `co_likes` where `wp_posts`.`ID` = `co_likes`.`id_post` and `co_likes`.`deleted_at` is null) as `like_count`,
             (select count(*) from `wp_comments` where `wp_posts`.`ID` = `wp_comments`.`comment_post_ID`) as `comment_count` ,
      from `wp_posts`
      where `post_type` in ('post', 'co_post') and `post_status` = 'publish'
     ) p
order by total_sum desc;

如果您只想按总和排序，但不需要看到它，可以将总和放入select：

      select `wp_posts`.ID ,
             (select count(*) from `co_likes` where `wp_posts`.`ID` = `co_likes`.`id_post` and `co_likes`.`deleted_at` is null) as `like_count`,
             (select count(*) from `wp_comments` where `wp_posts`.`ID` = `wp_comments`.`comment_post_ID`) as `comment_count` ,
      from `wp_posts`
      where `post_type` in ('post', 'co_post') and `post_status` = 'publish'
      order by (like_count + comment_count) desc

关于mysql - SQL添加列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46546182/