MySQL取每組的前N條記錄

標籤：

一、對分組的記錄取前N條記錄：例子：取前 2條最大(小)的記錄

 1 1.用子查詢： 2 SELECT * FROM right2 a  WHERE 2> 3 (SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND b.account>a.account) 4 ORDER BY a.id,a.account DESC 5 2.用exists半串連： 6 SELECT * FROM right2 a  WHERE EXISTS 7 (SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND a.account<b.account HAVING COUNT(*)<2) 8 ORDER BY a.id,a.account DESC 9 同理可以取組內最小的N條記錄：10 SELECT * FROM right2 a  WHERE 2>11 (SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND b.account<a.account)12 ORDER BY a.id,a.account DESC13 用exists：14 SELECT * FROM right2 a  WHERE EXISTS15 (SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND a.account>b.account HAVING COUNT(*)<2)16 ORDER BY a.id,a.account DESC

 如果取每組的最大(小)一條記錄我常用：

1 select id,val from t b inner join(select * from t a where  order by val desc) a  on a.id=b.id group by a.id order  by id;

 

二.執行個體：取每組最大的前 N條

 1 create table t2 ( 2   id int primary key,        3   gid char,  4   col1 int,  5   col2 int   6 ) engine=innodb;  7 insert into tx01 values 8 (1,‘A‘,31,6),  9 (2,‘B‘,25,83), 10 (3,‘C‘,76,21), 11 (4,‘D‘,63,56), 12 (5,‘E‘,3,17), 13 (6,‘A‘,29,97), 14 (7,‘B‘,88,63), 15 (8,‘C‘,16,22), 16 (9,‘D‘,25,43), 17 (10,‘E‘,45,28), 18 (11,‘A‘,2,78), 19 (12,‘B‘,30,79), 20 (13,‘C‘,96,73), 21 (14,‘D‘,37,40), 22 (15,‘E‘,14,86), 23 (16,‘A‘,32,67), 24 (17,‘B‘,84,38), 25 (18,‘C‘,27,9), 26 (19,‘D‘,31,21), 27 (20,‘E‘,80,63), 28 (21,‘A‘,89,9), 29 (22,‘B‘,15,22), 30 (23,‘C‘,46,84), 31 (24,‘D‘,54,79), 32 (25,‘E‘,85,64), 33 (26,‘A‘,87,13), 34 (27,‘B‘,40,45), 35 (28,‘C‘,34,90), 36 (29,‘D‘,63,8), 37 (30,‘E‘,66,40), 38 (31,‘A‘,83,49), 39 (32,‘B‘,4,90), 40 (33,‘C‘,81,7), 41 (34,‘D‘,11,12), 42 (35,‘E‘,85,10), 43 (36,‘A‘,39,75), 44 (37,‘B‘,22,39), 45 (38,‘C‘,76,67), 46 (39,‘D‘,20,11), 47 (40,‘E‘,81,36); 48 create table tx01 (49   id int primary key, 50   gid char, 51   col1 int, 52   col2 int  53 ) engine=innodb;

取每組gid 最大的前N條記錄：使用自串連或則半串連：

*N=1時：

自串連：降序排好後group by取每組最大的一條。

select * from (select * from t2 order by col2 desc)as a group by gid order by gid;

半串連方式：找不到比最大值還大的。

select * from t2 a where not exists(select 1 from t2 b where b.gid=a.gid and b.col2>a.col2) order by a.gid; 

 

*N=3時：

自串連：

select * from t2 a where 3>(select count(*) from t2 where gid=a.gid and col2>a.col2) order by a.gid,a.col2 desc;

半串連：

select * from t2 a where exists(select count(*) from t2 b where b.gid=a.gid and a.col2<b.col2 having(count(*))<3) order by a.gid,a.col2 desc

 

轉：本文出自 http://huanghualiang.blog.51cto.com/6782683/1252630

 

MySQL取每組的前N條記錄