ubuntu 8.10 安装Oracle 10G 企业版综合指南

数据库的产生（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)这个值的含义究竟是什么呢？
请您帮忙解答一下，谢谢

数据库的产生（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)这个值的含义究竟是什么呢？
请您帮忙解答一下，谢谢

数据库的产生（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)这个值的含义究竟是什么呢？
请您帮忙解答一下，谢谢