需要一个sql查询来找到评论最多的帖子,按评论数/计数DESC排序[英] Need a sql query to find posts with most commented order by comments number/count DESC

本文是小编为大家收集整理的关于需要一个sql查询来找到评论最多的帖子,按评论数/计数DESC排序的处理方法,想解了需要一个sql查询来找到评论最多的帖子,按评论数/计数DESC排序的问题怎么解决?需要一个sql查询来找到评论最多的帖子,按评论数/计数DESC排序问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

\d 个帖子

                                   Table "public.posts"
   Column    |          Type          |                     Modifiers                      
-------------+------------------------+----------------------------------------------------
 id          | integer                | not null default nextval('posts_id_seq'::regclass)
 title       | character varying(100) | not null
 content     | character varying(500) | not null
 created_at  | date                   | 
 updated_at  | date                   | 
 tags        | character varying(55)  | not null default '50'::character varying
 category_id | integer                | not null default 1
Indexes:
    "posts_pkey" PRIMARY KEY, btree (id)

\d 条评论

                                   Table "public.comments"
   Column   |          Type          |                       Modifiers                       
------------+------------------------+-------------------------------------------------------
 id         | integer                | not null default nextval('comments_id_seq'::regclass)
 post_id    | integer                | not null
 name       | character varying(255) | not null
 email      | character varying(255) | not null
 content    | character varying(500) | not null
 created_at | date                   | 
 updated_at | date                   | 
Indexes:
    "comments_pkey" PRIMARY KEY, btree (id)

我需要一个 sql 查询来查找评论最多的帖子.我该怎么做?

推荐答案

tsql 中你会做以下事情,我希望它能引导你朝着正确的方向前进

SELECT
         p.id,
         c.postcount
    FROM posts as p
    INNER JOIN (
                  SELECT
                         post_id,
                         count(*) AS postcount
                  FROM comments
                  GROUP BY post_id
               ) as c
           on p.id = c.post_id

    Order by c.postcount desc

本文地址:https://www.itbaoku.cn/post/1764069.html