数据库的产生(mysql)
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,2,
(11,'A',2,7,
(12,'B',30,79),
(13,'C',96,73),
(14,'D',37,40),
(15,'E',14,86),
(16,'A',32,67),
(17,'B',84,3,
(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,,
(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);
需要的结果
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 |
+----+------+------+------+
有一种解法,
SELECT a.id,a.gid,a.col1,a.col2
FROM t2 a,t2 b
where a.gid=b.gid AND a.col2 <=b.col2
GROUP BY a.id,a.gid,a.col1,a.col2
HAVING COUNT(b.id) <=3
ORDER BY a.gid,a.col2 desc
我看不懂,请高手解答一下,我自己也找到了一点线索,
mysql> SELECT *,count(b.id)
-> FROM t2 a,t2 b
-> where a.gid=b.gid AND a.col2 <=b.col2
-> GROUP BY a.id,a.gid,a.col1,a.col2
-> ORDER BY a.gid,a.col2 desc;
+----+------+------+------+----+------+------+------+-------------+
| id | gid | col1 | col2 | id | gid | col1 | col2 | count(b.id) |
+----+------+------+------+----+------+------+------+-------------+
| 6 | A | 29 | 97 | 6 | A | 29 | 97 | 1 |
| 11 | A | 2 | 78 | 6 | A | 29 | 97 | 2 |
| 36 | A | 39 | 75 | 6 | A | 29 | 97 | 3 |
| 16 | A | 32 | 67 | 6 | A | 29 | 97 | 4 |
| 31 | A | 83 | 49 | 6 | A | 29 | 97 | 5 |
| 26 | A | 87 | 13 | 6 | A | 29 | 97 | 6 |
| 21 | A | 89 | 9 | 6 | A | 29 | 97 | 7 |
| 1 | A | 31 | 6 | 1 | A | 31 | 6 | 8 |
| 32 | B | 4 | 90 | 32 | B | 4 | 90 | 1 |
| 2 | B | 25 | 83 | 2 | B | 25 | 83 | 2 |
| 12 | B | 30 | 79 | 2 | B | 25 | 83 | 3 |
| 7 | B | 88 | 63 | 2 | B | 25 | 83 | 4 |
| 27 | B | 40 | 45 | 2 | B | 25 | 83 | 5 |
| 37 | B | 22 | 39 | 2 | B | 25 | 83 | 6 |
| 17 | B | 84 | 38 | 2 | B | 25 | 83 | 7 |
| 22 | B | 15 | 22 | 2 | B | 25 | 83 | 8 |
| 28 | C | 34 | 90 | 28 | C | 34 | 90 | 1 |
| 23 | C | 46 | 84 | 23 | C | 46 | 84 | 2 |
| 13 | C | 96 | 73 | 13 | C | 96 | 73 | 3 |
| 38 | C | 76 | 67 | 13 | C | 96 | 73 | 4 |
| 8 | C | 16 | 22 | 8 | C | 16 | 22 | 5 |
| 3 | C | 76 | 21 | 3 | C | 76 | 21 | 6 |
| 18 | C | 27 | 9 | 3 | C | 76 | 21 | 7 |
| 33 | C | 81 | 7 | 3 | C | 76 | 21 | 8 |
| 24 | D | 54 | 79 | 24 | D | 54 | 79 | 1 |
| 4 | D | 63 | 56 | 4 | D | 63 | 56 | 2 |
| 9 | D | 25 | 43 | 4 | D | 63 | 56 | 3 |
| 14 | D | 37 | 40 | 4 | D | 63 | 56 | 4 |
| 19 | D | 31 | 21 | 4 | D | 63 | 56 | 5 |
| 34 | D | 11 | 12 | 4 | D | 63 | 56 | 6 |
| 39 | D | 20 | 11 | 4 | D | 63 | 56 | 7 |
| 29 | D | 63 | 8 | 4 | D | 63 | 56 | 8 |
| 15 | E | 14 | 86 | 15 | E | 14 | 86 | 1 |
| 25 | E | 85 | 64 | 15 | E | 14 | 86 | 2 |
| 20 | E | 80 | 63 | 15 | E | 14 | 86 | 3 |
| 30 | E | 66 | 40 | 15 | E | 14 | 86 | 4 |
| 40 | E | 81 | 36 | 15 | E | 14 | 86 | 5 |
| 10 | E | 45 | 28 | 10 | E | 45 | 28 | 6 |
| 5 | E | 3 | 17 | 5 | E | 3 | 17 | 7 |
| 35 | E | 85 | 10 | 5 | E | 3 | 17 | 8 |
+----+------+------+------+----+------+------+------+-------------+
40 rows in set (0.00 sec)
注意看这个表的最后一列,好像奥秘在这里,count(b.id)=1,取出的就是每一组中最大的,count(b.id)=2,取出的就是每一组中第二大的,count(b.id)<=3,就是前三个,
问题是,我不理解的地方,count(b.id)这个值的含义究竟是什么呢?
请您帮忙解答一下,谢谢
分组取最大N条记录
Web、Mail、Ftp、DNS、Proxy、VPN、Samba、LDAP 等基础网络服务
前往
- 公告/注意事项
- ↳ 新闻和通知
- ↳ 软件推荐
- 系统安装
- ↳ 教学和常见问答
- ↳ 系统安装和升级
- ↳ 启动和引导
- ↳ 网卡无线拨号
- ↳ 硬件与内核
- ↳ 笔记本及便携设备
- ↳ Ubuntu衍生版
- ↳ 其他类Unix OS发行版
- ↳ Debian发行版
- 配置美化
- ↳ 中文显示和输入法
- ↳ 个人配置文件存放点
- ↳ 软件文档翻译
- ↳ 窗口管理器及美化
- ↳ 桌面展示
- 软件使用
- ↳ 办公、图像、机械电子设计等
- ↳ Vim和Emacs
- ↳ 开源模板库
- ↳ 互联网相关软件
- ↳ 影音多媒体
- ↳ 游戏和模拟器
- ↳ 虚拟机和虚拟化
- ↳ Wine及其分支
- ↳ 其它类软件
- 服务器管理
- ↳ 服务器运维
- ↳ 云计算和容器(K8S/KVM/Docker/WSL等)
- ↳ Ubuntu VPS
- 程序设计与开发
- ↳ Shell脚本
- ↳ 软件/网站开发
- ↳ 内核及嵌入式开发
- ↳ Ubuntu编译和打包
- ↳ 开源小工具
- ↳ 挑战任务
- 感想与交流
- ↳ 深度PK版
- ↳ Ubuntu故事和感慨
- ↳ 同城交流
- ↳ 校园社团
- ↳ 港澳台校区
- ↳ 国外校区
- 站务区
- ↳ 论坛管理
- ↳ Ubuntu中文网上商店