Oracle语句：来自同一个城市人数最多的前两个城市id

本文链接：https://blog.youkuaiyun.com/cai_xiaomu/article/details/105965120

创建表

Create table Person (PersonId int, FirstName varchar(255), LastName varchar(255),AddressId int);
Create table Address (AddressId int, City varchar(255), State varchar(255));

插入Peson语句：

insert into Person (PersonId, LastName, FirstName,AddressId) values ('1', 'Wang', 'Allen',3);
insert into Person (PersonId, LastName, FirstName,AddressId) values ('2', 'Zhang', 'Mia',2);
insert into Person (PersonId, LastName, FirstName,AddressId) values ('3', 'Michael', 'Jordan',2);
insert into Person (PersonId, LastName, FirstName,AddressId) values ('4', 'Kobe', 'Bryant',1);
insert into Person (PersonId, LastName, FirstName,AddressId) values ('5', 'Able', 'Smith',1);
insert into Person (PersonId, LastName, FirstName,AddressId) values ('6', 'Bill', 'Jones',3);
insert into Person (PersonId, LastName, FirstName,AddressId) values ('7', 'Bob', 'Brown',1);

插入Address语句：

insert into Address (AddressId, City, State) values ('1', 'New York City', 'New York');
insert into Address (AddressId, City, State) values ('2', 'Orlando City', 'Florida');
insert into Address (AddressId, City, State) values ('3', 'San Francisco City', 'California');

查询语句：

select rownum,addressid
from 
  (select addressid,count(addressid)
  from  Person

  group by addressid
  order by count(addressid)desc
  )where rownum<2