sql - postgresql:需要使用sql查询来查找一个类别中有多少个帖子

\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.categories"
    Column     |         Type          |                        Modifiers
---------------+-----------------------+---------------------------------------------------------
 id            | integer               | not null default nextval('categories_id_seq'::regclass)
 category_name | character varying(50) | not null
 created_at    | date                  |
 updated_at    | date                  |
Indexes:
    "categories_pkey" PRIMARY KEY, btree (id)

需要计算和得到多少职位有一个类别。我该怎么做？

最佳答案

SELECT c.id, c.category_name, COUNT(p.id)
   FROM public.categories c
       LEFT JOIN public.posts p
           ON c.id = p.category_id
   GROUP BY c.id, c.category_name

关于sql - postgresql:需要使用sql查询来查找一个类别中有多少个帖子，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/8509563/