这是csdn的一个老贴子,有看不懂的地方,特此请教。
数据库的产生(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)这个值的含义究竟是什么呢?
请您帮忙解答一下,谢谢
| 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 |
为何| 6 | A | 29 | 97 | 6 | A | 29 | 97 | 这条记录的b.id为何是1?
| 11 | A | 2 | 78 | 6 | A | 29 | 97 |这条记录的b.id为何是2?
| id | gid | col1 | col2 | id | gid | col1 | col2 | count(b.id) |
+----+------+------+------+----+------+------+------+-------------+
| 6 | A | 29 | 97 | 6 | A | 29 | 97 | 7 |
| 11 | A | 2 | 78 | 6 | A | 29 | 97 | 7 |
| 36 | A | 39 | 75 | 6 | A | 29 | 97 | 7 |
b.id=6的有7个
当a是
| id | gid | col1 | col2 |
+----+------+------+------+
| 6 | A | 29 | 97 | 6 | A |
满足a.gid=b.gid AND a.col2 <=b.col2的b只有一条
+----+------+------+------+----+------+------+------+
| id | gid | col1 | col2 | id | gid | col1 | col2 |
+----+------+------+------+----+------+------+------+
| 6 | A | 29 | 97 | 6 | A | 29 | 97 |
当a是
| 11 | A | 2 | 78
满足a.gid=b.gid AND a.col2 <=b.col2的b有2条
11 | A | 2 | 78 |
6 | A | 29 | 97 |
以此类推