一、准备测试的表和数据
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)