Mysql和Oracl 分组取每组前N条记录

MySQL取每组的前N条记录：使用自连接的方式

一、对分组的记录取前N条记录：例子：取前 2条最大(小)的记录

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

1.用子查询： SELECT  *  FROM  right2 a   WHERE  2> ( SELECT  COUNT (*)  FROM  right2 b  WHERE  b.id=a.id  AND  b.account>a.account) ORDER  BY  a.id,a.account  DESC 2.用exists半连接： SELECT  *  FROM  right2 a   WHERE  EXISTS ( SELECT  COUNT (*)  FROM  right2 b  WHERE  b.id=a.id  AND  a.account<b.account  HAVING  COUNT (*)<2) ORDER  BY  a.id,a.account  DESC 同理可以取组内最小的N条记录： SELECT  *  FROM  right2 a   WHERE  2> ( SELECT  COUNT (*)  FROM  right2 b  WHERE  b.id=a.id  AND  b.account<a.account) ORDER  BY  a.id,a.account  DESC 用exists： SELECT  *  FROM  right2 a   WHERE  EXISTS ( SELECT  COUNT (*)  FROM  right2 b  WHERE  b.id=a.id  AND  a.account>b.account  HAVING  COUNT (*)<2) ORDER  BY  a.id,a.account  DESC SQLServer支持 top -N： select  a.*  from  tb a  where  val = ( select  top  3 val  from  tb  where  name  = a. name )  order  by  a. name

如果取每组的最大(小)一条记录我常用：

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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

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

取每组gid 最大的前N条记录：使用自连接或则半连接：

*N=1时：

自连接：降序排好后group by取每组最大的一条。

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

半连接方式：找不到比最大值还大的。

1	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时：

自连接：

1	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 ;

半连接：

1	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

Oracle取每组的前N条记录:可以使用分析函数，hive中也能使用

SELECT * FROM(
SELECT z.type , z.code ,ROW_NUMBER()
OVER(PARTITION BY z.type ORDER BY z.code desc) AS code_id
FROM group_info z
)
WHERE code_id <4;   取每组最大的前四条记录