Oracle 表的分组操作

需求：

1.查出每组的前几条数据;

2.查出每组数据条数满足一定数目的数据;

 

数据库建表以及插入数据sql如下

CREATE TABLE request (
  buyer_company_name varchar(100),
  buyer_com_id int,
  buyer_com_country varchar(200),
  subject varchar(200)
);
INSERT INTO request VALUES ('Media101', 1001, 'Australia', 'Australia_1');
INSERT INTO request VALUES ('Media101', 1001, 'Australia', 'Australia_2');
INSERT INTO request VALUES ('Media101', 1001, 'Australia', 'Australia_3');
INSERT INTO request VALUES ('Media102', 1002, 'Australia', 'Australia_1');
INSERT INTO request VALUES ('Media102', 1002, 'Australia', 'Australia_2');
INSERT INTO request VALUES ('Media102', 1002, 'Australia', 'Australia_3');
INSERT INTO request VALUES ('Media103', 1003, 'Australia', 'Australia_1');
INSERT INTO request VALUES ('Media104', 1004, 'Australia', 'Australia_1');
INSERT INTO request VALUES ('Media105', 1005, 'Australia', 'Australia_1');
INSERT INTO request VALUES ('ali101', 1006, 'India', 'India_1');
INSERT INTO request VALUES ('ali102', 1007, 'India', 'India_1');
INSERT INTO request VALUES ('ali103', 1008, 'India', 'India_1');
INSERT INTO request VALUES ('ali103', 1008, 'India', 'India_2');
INSERT INTO request VALUES ('ali103', 1008, 'India', 'India_3');
INSERT INTO request VALUES ('ali103', 1008, 'India', 'India_4');
INSERT INTO request VALUES ('ali104', 1009, 'India', 'India_1');

 

具体：

1. 查询出每个国家的2个公司

   一开始会这么写，这是有问题的，查询出的结果会少于2个，即使加了distinct去重。不知道为什么，忘知情者留言

select *
  from request a
 where buyer_com_id in (select distinct buyer_com_id
                          from request b
                         where b.buyer_com_country = a.buyer_com_country
                           and rownum <= 2)

结果：

正确的写法如下：

select *
  from request a
 where buyer_com_id in
       (select buyer_com_id
          from (select distinct buyer_com_id, buyer_com_country from request) b
         where b.buyer_com_country = a.buyer_com_country
           and rownum <= 2)

2.查询出每个国家的发布信息条数在2条以上的2个公司

有了以上的基础，这个sql就很好写了

select *
  from request a
 where buyer_com_id in (select buyer_com_id
                          from (select buyer_com_id, buyer_com_country
                                  from request
                                having count(*) > 2
                                 group by buyer_com_country, buyer_com_id) b
                         where b.buyer_com_country = a.buyer_com_country
                           and rownum <= 2)

2.1 如果有个要求竟敢是: 查询出每个国家的2个公司，再找出发布信息条数在2条以上的

那只能拿出这个丑陋的sql来了

select *
  from request
 where buyer_com_id in
       (select buyer_com_id
          from request a
         where buyer_com_id in
               (select buyer_com_id
                  from (select distinct buyer_com_id, buyer_com_country
                          from request) b
                 where b.buyer_com_country = a.buyer_com_country
                   and rownum <= 2) having count(*) > 2
         group by buyer_com_id)

结果为：

如果你有更简洁的 或者不同的sql，可以实现相同的效果，欢迎留言