SQL 如何在连接中使用 distinct on 和 count 函数

Question

我是 sql 的新手，正在使用 postgres 9.6，我有 2 个查询，我想将它们合并到 1 个中。我想使用 Count 函数并按我在此处完成的字段降序排序

第一次查询

select count(s.city)::text as most_topics,
city::text || ', ' || state::text AS location from streams s 
group by (location) order by most_topics desc limit 5

第一个查询正是我想要的信息，顺序完全相同，问题是我需要进行内部联接并从我在此处完成的第二个表中获取数据。第二个查询获取我需要的所有数据，但是我不知道如何在第二个查询中使用 Count() 函数，任何建议都很好。总而言之，第一个查询将我想要的数据减半，因为我需要在字段 (city and state) 上加入另一个名为 zips 的数据。第二个查询为我提供了我需要的所有数据，但我似乎无法使用 Count() 函数。

第二次查询

select DISTINCT ON(s.city,s.state)s.city as location,
z.latitudes,z.statelong,z.longitudes,z.thirtylatmin,z.thirtylatmax
 ,z.longitudes,z.thirtylatmin,z.thirtylatmax,     
 z.thirtylonmin,z.thirtylonmax from streams s inner join zips z
 on (s.city=z.city and s.state=z.state) where  order by s.city,s.state desc limit 5

第一次查询结果

第二个查询结果:Lutz 应该在顶部而不是底部

Answer 1

您可以使用 CTE 或子查询:

with l5 as (
      select count(s.city)::text as most_topics,
             city::text || ', ' || state::text AS location,
             city, state
      from streams s 
      group by city, state
      order by most_topics desc
      limit 5
     ) 
select distinct on (l5.locatin) l5.location, l5.most_topics, z.*
from l5 join
     zips z
     on l5.city = z.city and l5.state = z.state
order by l5.location;