SQL 查询从我现有的数据表中获取州内城市人口最多的邮政编码

Question

我有一个表“People”，其中包含以下列：id、fname、lname、addr、city、state、zip 在 Oracle 9.x 中，

我想获取每个州每个城市人口最多的邮政编码，

我写了这个查询：

Select City, State, Zip, count(*)
From People
Group By City, State
Order By count(*)

但它为一个州的城市提供了多个行，例如（编造）：-

City  --  State -- Zip   -- Count(*)
City0 --  ST0   -- 32111 -- 50
City1 --  ST1   -- 11223 -- 100
City1 --  ST1   -- 11225 -- 90
City1 --  ST1   -- 11226 -- 50
City2 --  ST1   -- 11255 -- 70
City3 --  ST2   -- 55443 -- 60

我尝试了 HAVING 子句，例如：having Count () = max(Count()) 但收到错误消息：嵌套子句太深或某些内容（Oracle 9.x）

我只想要顶部行：- 城市1 -- ST1 -- 11223 -- 100 对于所有 City1 - ST1 行，每个州每个城市只有一行的其他行将保持不变。想要的输出：-

City  --  State -- Zip   -- Count(*)
City0 --  ST0   -- 32111 -- 50
**City1 --  ST1   -- 11223 -- 100**
City2 --  ST1   -- 11255 -- 70
City3 --  ST2   -- 55443 -- 60

我怎样才能实现这个目标？感谢您的观看。

==========

从 JIM HUDSON 的回复中找到答案 通过稍微修改提供的查询，我得到了正确的结果。最终代码：-

select city, state, zip, counter from  (
select city, state, zip, count(*) as counter from people group by city, state, zip
)  
where counter = (
    select max(count2) from (
        select city as city1, state as state1, zip as zip1, count(*) as count2 from people group by city, state, zip   )
    where city=city1 and state=state1
);

Answer 1

select * from (
  select city, state, zip,
    rank() over (partition by city, state order by cnt desc) rank
  from (
    Select City, State, Zip, count(*) cnt
    From People
    Group By City, State, Zip
  )
)
where rank = 1

Answer 2

将您的第一个查询视为一个视图。它为您提供城市、州、邮政编码的计数。然后以此为起点。这样就很容易让人口集中在最大的区域。

例如，

select city, state, max(counter) from
(select city, state, zip, count(*) as counter from people group by city, state, zip)
group by city, state;

当然，这并不完全是您想要的，因为您想知道那是哪个邮政编码。所以旧的方法就像

select city, state, zip, counter from
(select city, state, zip, count(*) as counter from people group by city, state, zip)
where counter = (select max(count2) from (select city, state, zip, count(*) as count2 from people group by city, state, zip));

有其他方法可以使用分析函数来执行第二步。我会让其他人来做这些工作。但关键的步骤是创建内联视图并将其用作进一步分析的基础。