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，可以實現相同的效果，歡迎留言

 

Oracle 表的分組操作