# [征集]分组取最大N条记录方法征集，及散分(新年，升星)

ACMAIN_CHM IT  2009-12-31 04:30:37

2009 的最后一天，升了一颗星，同时也祝大家新年快乐。

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

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 |
+----+------+------+------+

1）不限数据库，但请说明，比如 Oracle Database 10g 10.2 , MySQL 5.1.33
2) 不限方法， SQL语句，存储过程。
...全文
27150 145 2 打赏 收藏 举报

145 条回复

[Quote=引用 41 楼 的回复:]

[/Quote]

• 打赏
• 举报

jazzee 2012-09-05

• 打赏
• 举报

jinguanding 2012-04-06

MySQL数据库InnoDB存储引擎Log漫游 简述：详细分析了InnoDB存储引擎Checkpoint技术，大家可以仔细研读！
• 打赏
• 举报

panyulirong 2012-03-01

• 打赏
• 举报

lygcw9602 2012-02-14

123456
• 打赏
• 举报

qqalex 2012-01-11

• 打赏
• 举报

petrie 2011-10-25
``````select t2.id,t2.gid,t2.col1,t2.col2 from t2,
(select id,max(col2) from t2 group by gid) tv2
where t2.id=tv2.id``````
• 打赏
• 举报

petrie 2011-10-25

• 打赏
• 举报

[Quote=引用 3 楼 zhoupuyue 的回复:]

mysql:5.0.45-community-nt

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

2)
select * from t2 a where
3>(select count(*) from t2 where ……
[/Quote]顶一个
• 打赏
• 举报

fixed 2011-06-17

• 打赏
• 举报

• 打赏
• 举报

fw5332 2011-05-27

• 打赏
• 举报

wh19780506 2011-05-20

• 打赏
• 举报

DUCK6 2011-03-02

• 打赏
• 举报

[Quote=引用 3 楼 zhoupuyue 的回复:]

mysql:5.0.45-community-nt

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

[/Quote]

• 打赏
• 举报

slipper520 2010-11-24

• 打赏
• 举报

Ivy_napoloan 2010-11-22

• 打赏
• 举报

anysun56 2010-11-16
very good
• 打赏
• 举报

hechao19867187 2010-03-31
[Quote=引用 16 楼 dqlmj2009 的回复:]

mysql5.1.41

1) N=1 取GID每组 COL2最大的记录
SELECT a.id,a.gid,a.col1,a.col2
FROM t2 as a,t2 as b
where a.gid=b.gid AND a.col2 <=b.col2
GROUP BY a.id,a.gid,a.col1,a.col2
having a.col2>……
[/Quote]

• 打赏
• 举报

WALLW1986 2010-03-30

• 打赏
• 举报

MySQL

5.5w+

MySQL相关内容讨论专区

2009-12-31 04:30