oracle/mysql TOP/Button N查询

9ud4c41lnn

　　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
;
 
查询结果：

 
参考：http://www.blogjava.net/pengpenglin/archive/2008/06/27/211019.html

http://thenoyes.com/littlenoise/?p=36