Mysql中exists子查询语句的使用，取出每组中最高的前n名的信息

一、准备测试的表和数据

 

create table cat(
    id int not null auto_increment primary key,
    cat_id int,
    value int,
    name varchar(30)
);

二、插入一些测试数据

 

 

insert into cat (cat_id,name,value) values ('1','name1', '2');
insert into cat (cat_id,name,value) values ('1','name2', '21');
insert into cat (cat_id,name,value) values ('1','name3', '1');
insert into cat (cat_id,name,value) values ('1','name4', '3');
insert into cat (cat_id,name,value) values ('2','name5', '54');
insert into cat (cat_id,name,value) values ('2','name6', '4');
insert into cat (cat_id,name,value) values ('2','name7', '24');
insert into cat (cat_id,name,value) values ('2','name8', '23');
insert into cat (cat_id,name,value) values ('3','name9', '57');
insert into cat (cat_id,name,value) values ('3','name10','45');
insert into cat (cat_id,name,value) values ('3','name11','12');
insert into cat (cat_id,name,value) values ('3','name12','23');

基本工作准备完毕，回到正题，在实际开发环境中，有时需要查询每个分组例如销量最高的前几名等等。可以使用如下SQL语句(此处是Mysql):

 

 

取出每组中value值最高的前三条记录：

select a.* from cat a where exists (select count(*) from cat where cat_id = a.cat_id and value > a.value having Count(*) < 3) order by a.cat_id,a.value desc;

 

exists的子查询中，有引用外层查询，现在来分析：

 

首先，按照sql执行顺序，先看from部分，毫无疑问，查询了所有的表记录，等同于select * from cat，记录如下：

 

mysql> select * from cat;
+----+--------+--------+-------+
| id | cat_id | name   | value |
+----+--------+--------+-------+
|  1 |      1 | name1  |     2 |
|  2 |      1 | name2  |    21 |
|  3 |      1 | name3  |     1 |
|  4 |      1 | name4  |     3 |
|  5 |      2 | name5  |    54 |
|  6 |      2 | name6  |     4 |
|  7 |      2 | name7  |    24 |
|  8 |      2 | name8  |    23 |
|  9 |      3 | name9  |    57 |
| 10 |      3 | name10 |    45 |
| 11 |      3 | name11 |    12 |
| 12 |      3 | name12 |    23 |
+----+--------+--------+-------+
12 rows in set (0.00 sec)

然后，exits子查询的特点是返回true或者是false，并不会返回结果集，这点很重要！等同于外层查询通过where了一个exists的子查询返回的是true还是false来判断外层查询的这条记录是否保存到结果集中。

 

进一步了解执行的过程：

现在，我拿到外层查询的第一行，也就是上图的第一行。此时子查询中取出当前表cat中cat_id=1并且values值大于2的，毫无疑问，有2条记录，满足条件小于3(用到了having语句，对中间结果进行过滤等等处理)，此时exists满足条件，返回true。外层查询第一条记录被保存。到最后cat_id为1，value为1的，count(*)为3，子查询返回的是false。所以上图中的第3条记录不会被保存。

 

然后一番选取之后，外层查询得到了满足条件的结果集。最后通过order by将结果集排序，然后显示如下：

 

+----+--------+--------+-------+
| id | cat_id | name   | value |
+----+--------+--------+-------+
|  2 |      1 | name2  |    21 |
|  4 |      1 | name4  |     3 |
|  1 |      1 | name1  |     2 |
|  5 |      2 | name5  |    54 |
|  7 |      2 | name7  |    24 |
|  8 |      2 | name8  |    23 |
|  9 |      3 | name9  |    57 |
| 10 |      3 | name10 |    45 |
| 12 |      3 | name12 |    23 |
+----+--------+--------+-------+
9 rows in set (0.00 sec)