oracle mysql top_oracle/mysql TOP/Button N查询

oracle里面要获取每个分组里面的topN可以采用：

select *

from (select emp_id, name, occupation,

rank() over ( partition by occupation order by emp_id) rank

from employee)

where rank <= 3

select * from

(select emp_id, name, occupation,rank() over (

partition by occupation order by emp_id,RowNum) rank

from employee)

where rank <= 3

mysql：分组之后取topN的sql：

Here’s another form of the solution without subquery that also resolves matching ratings by using the primary key:

DROP DATABASE IF EXISTS topn;

CREATE DATABASE topn;

USE topn;

CREATE TABLE theTable (

pKey int PRIMARY KEY

, groupId int

, rating int

);

INSERT INTO theTable (

pKey

, groupId

, rating

) VALUES

( 1, 1, 55 )

, ( 2, 1, 53 )

, ( 3, 1, 51 )

, ( 4, 1, 59 )

, ( 5, 1, 58 )

, ( 6, 1, 58 )

, ( 7, 1, 53 )

, ( 8, 1, 55 )

, ( 9, 1, 55 )

, ( 10, 1, 53 )

, ( 11, 2, 52 )

, ( 12, 2, 53 )

, ( 13, 2, 54 )

, ( 14, 2, 59 )

, ( 15, 2, 58 )

, ( 16, 2, 57 )

, ( 17, 2, 56 )

;

SELECT t1.pKey

, t1.rating

, t1.groupId

, COUNT(t2.pKey) AS cnt

FROM theTable AS t1

LEFT JOIN theTable AS t2

ON (t1.rating, t1.pKey) <= (t2.rating, t2.pKey)

AND t1.groupId = t2.groupId

GROUP BY t1.pKey

, t1.rating

, t1.groupId

HAVING cnt <= 5

ORDER BY t1.groupId, cnt

;

查询结果：