如何从映射表组中获取计数成为PostgreSQL中的另一个表？ [英] How to get count from mapping table group be another table in PostgreSQL?

问题描述

我有三个表：

store 
=====
   name
   address 
   city 
   state 
   country 
   tag ..., 

post
=======
    title
    summary 
    tags ...

store_post_map
================
(mapping on store and post based on tag).

现在我想按 city <获取来自映射表组的帖子数/ code>，州，国家或 store.id ，PostgreSQL中的SQL是什么？

Now I want to get count of posts from mapping table group by city, state, country or store.id, what to be the SQL in PostgreSQL?

推荐答案

基本上是这样的：

SELECT store_id, count(*) AS posts_ct
FROM   store_post_map
GROUP  BY store_id;

我们如何获取每个城市，州或国家/地区的计数区域
可以有多个商店？

How can we get counts for each city, state or country where each area can have multiple stores?

每个国家/地区的数量：

Count per country:

SELECT s.country, count(*) AS posts_ct
FROM   store          s
JOIN   store_post_map sp ON sp.store_id = s.id
GROUP  BY 1; -- positional parameter - is the same as GROUP BY s.country here

每个城市，您可能需要 GROUP BY 区域和国家，因为城市名称很难唯一。像这样：

For the count per city you may have to GROUP BY area and country in addition since a city name is hardly unique. Like:

SELECT s.city, s.area, s.country, count(*) AS posts_ct
FROM   store          s
JOIN   store_post_map sp ON sp.store_id = s.id
GROUP  BY 1, 2, 3;

这篇关于如何从映射表组中获取计数成为PostgreSQL中的另一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！