mysql自连接分组实例取每组最大N条记录

发布时间:2019-08-07编辑:脚本学堂
本文介绍了mysql数据库中使用自连接方法,分组取得每组最大n条记录的方法,有需要的朋友参考下。

mysql自连接分组取每组最大N条记录

1、测试数据:
 

复制代码 代码示例:

create table t2 (
  id int primary key,
  gid char,
  col1 int,
  col2 int
) engine=myisam;

insert into t2 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);

2、问题:
期望结果
1) N=1 取GID每组 COL2最大的记录 
 

复制代码 代码示例:
+----+------+------+------+
  | id | gid | col1 | col2 |
  +----+------+------+------+
  | 6 | A | 29 | 97 |
  | 15 | E | 14 | 86 |
  | 24 | D | 54 | 79 |
  | 28 | C | 34 | 90 |
  | 32 | B | 4 | 90 |
  +----+------+------+------+
 

2) N=3 取GID每组 COL2最大的3条记录 
 

复制代码 代码示例:
  +----+------+------+------+
  | id | gid | col1 | col2 |
  +----+------+------+------+
  | 6 | A | 29 | 97 |
  | 11 | A | 2 | 78 |
  | 36 | A | 39 | 75 |
  | 32 | B | 4 | 90 |
  | 2 | B | 25 | 83 |
  | 12 | B | 30 | 79 |
  | 28 | C | 34 | 90 |
  | 23 | C | 46 | 84 |
  | 13 | C | 96 | 73 |
  | 24 | D | 54 | 79 |
  | 4 | D | 63 | 56 |
  | 9 | D | 25 | 43 |
  | 15 | E | 14 | 86 |
  | 25 | E | 85 | 64 |
  | 20 | E | 80 | 63 |
  +----+------+------+------+

3、答案:
 

1) 
select * from t2 a
where not exists
(select 1 from t2 where gid=a.gid and col2>a.col2);
或者:
select * from (select * from t2 order by gid,col2 desc) tgroup by gid;
2)
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;

类似问题:
http://topic.csdn.net/u/20120914/11/d93cca10-54e9-464b-a0fa-f67667374555.html
--------------------
 

复制代码 代码示例:

public class User{

//数据库表明也是User

private String userId;//id

private String userName;//用户名

private Date cd_time;注册时间

private User inviteUser; //邀请人(填的实际上邀请人的ID【外键】)

}

有如下数据,注册时间没有写,就是系统当前时间
1  张一  null
2  张二  null
3  张三  null
4  张四  null

5  张五  1    -------------表示张五是张一邀请来的

6  张六  2    -------------表示张刘是张二邀请来的

7  张七  3

8  张八  4

9  张九  5

10 王一  6

11 王二  6

12 王三  6

13 王四  1

14 王五  13

需求:按时间顺序,查询出前十条
像这样:
张一,2 张一邀请了2人
张二,1 张二邀请了1人
张三,1
...
张五,1
张六,3 张六邀请了3人
王四,1 王四邀请了1人
 

复制代码 代码示例:
select a.userName, count(b.userId)
from User a left join User b on a.userId = b.inviteUser
group by a.userName