Grouping operations for Oracle tables

Demand:

1. Identify the first few data for each group;

2. Identify the number of data bars per group to meet a certain number of data;

Database build table and insert data SQL as follows

CREATE TABLERequest (Buyer_company_namevarchar( -), buyer_com_idint, Buyer_com_countryvarchar( $), subjectvarchar( $));INSERT  intoRequestVALUES('Media101',1001,'Australia','australia_1');INSERT  intoRequestVALUES('Media101',1001,'Australia','australia_2');INSERT  intoRequestVALUES('Media101',1001,'Australia','Australia_3');INSERT  intoRequestVALUES('Media102',1002,'Australia','australia_1');INSERT  intoRequestVALUES('Media102',1002,'Australia','australia_2');INSERT  intoRequestVALUES('Media102',1002,'Australia','Australia_3');INSERT  intoRequestVALUES('Media103',1003,'Australia','australia_1');INSERT  intoRequestVALUES('Media104',1004,'Australia','australia_1');INSERT  intoRequestVALUES('Media105',1005,'Australia','australia_1');INSERT  intoRequestVALUES('ali101',1006,'India','india_1');INSERT  intoRequestVALUES('ali102',1007,'India','india_1');INSERT  intoRequestVALUES('ali103',1008,'India','india_1');INSERT  intoRequestVALUES('ali103',1008,'India','india_2');INSERT  intoRequestVALUES('ali103',1008,'India','India_3');INSERT  intoRequestVALUES('ali103',1008,'India','India_4');INSERT  intoRequestVALUES('ali104',1009,'India','india_1');

Specific:

1. Find out 2 companies in each country

In the beginning, This is problematic, the results of the query will be less than 2, even if the distinct to heavy. Do not know why, forget the person message

Select *   from  where in (selectdistinct  buyer_com_id                          from  Request B                         where=  a.buyer_com_country                           and <= 2)

Results:

The correct wording is as follows:

Select *   fromRequest awherebuyer_com_idinch       (Selectbuyer_com_id from (SELECT distinct buyer_com_id, Buyer_com_country from request) bwhereB.buyer_com_country=A.buyer_com_country andRowNum<= 2)

2. Find out the number of published information in each country more than 2 of the 2 companies

With the above foundation, this SQL is very well written.

Select *   fromRequest awherebuyer_com_idinch(Selectbuyer_com_id from(Selectbuyer_com_id, Buyer_com_country fromRequestHave count (*) > 2 Group by buyer_com_country, buyer_com_id) bwhereB.buyer_com_country=A.buyer_com_country andRowNum<= 2)

2.1 If there is a request for the dare is: the 2 companies in each country, and then find out the number of published information on more than 2 articles

That's the only ugly SQL I can get.

Select *   fromRequestwherebuyer_com_idinch       (Selectbuyer_com_id fromRequest awherebuyer_com_idinch               (Selectbuyer_com_id from(Select distinctbuyer_com_id, Buyer_com_country fromrequest) BwhereB.buyer_com_country=A.buyer_com_country andRowNum<= 2) having Count(*)> 2         Group  byBUYER_COM_ID)

The result is:

If you have more concise or different SQL, you can achieve the same effect, welcome message

Grouping operations for Oracle tables