mysql自连接分组查询最新_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*fromt2orderbygid,col2desc) tgroupbygid;

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;

类似的问题：

--------------------

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