Mysql相关子查询&&MySQL获取分组后的TOP N记录

小燕子，哈哈哈哈~~~~~~~~~~

 

相关子查询是指引用了外部查询列的子查询，即子查询会对外部查询的每行进行一次计算。

举个例子

root:test> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `course` varchar(20) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8


insert into test1(name,course,score)
values
('张三','语文',80),
('李四','语文',90),
('王五','语文',93),
('张三','数学',77),
('李四','数学',68),
('王五','数学',99),
('张三','英语',90),
('李四','英语',50),
('王五','英语',89);


root:test>  select * from test1;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  1 | 张三   | 语文   |    80 |
|  2 | 李四   | 语文   |    90 |
|  3 | 王五   | 语文   |    93 |
|  4 | 张三   | 数学   |    77 |
|  5 | 李四   | 数学   |    68 |
|  6 | 王五   | 数学   |    99 |
|  7 | 张三   | 英语   |    90 |
|  8 | 李四   | 英语   |    50 |
|  9 | 王五   | 英语   |    89 |
+----+--------+--------+-------+

使用相关子查询

root:test> select *
    -> from test1 a
    -> where 2>(select count(*) from test1 where course=a.course and score>a.score)
    -> order by a.course,a.score desc;
+----+--------+--------+-------+
| id | name   | course | score |
+----+--------+--------+-------+
|  6 | 王五   | 数学   |    99 |
|  4 | 张三   | 数学   |    77 |
|  7 | 张三   | 英语   |    90 |
|  9 | 王五   | 英语   |    89 |
|  3 | 王五   | 语文   |    93 |
|  2 | 李四   | 语文   |    90 |
+----+--------+--------+-------+
rows in set (0.01 sec)

分析下这个sql：

select * from test1 a where 2 > (select count(*) from test1 where course=a.course and score>a.score)

相关子查询的特点就是子查询依赖与外部查询，在这里面其实是 select * from test 已经先执行了一遍了，查出了所有的数据

然后相关子查询针对每一行数据进行select count(*) from test1 where course=a.course and score>a.score

例如：

　　第一行是张三，数学77，那么相关子查询做的工作就是找出test表所有课程是数学的行，查询 张三，77|李四，68|王五，99

　　然后where条件score>77，查询出王五，99，count=1，这时候外部条件2>1,符合。

　　第二行是李四，数学68，那么相关子查询做的工作就是找出test表所有课程是数学的行，查询 张三，77|李四，68|王五，99

　　然后where条件score>68，查询出张三，77,王五，99，count=2，这时候外部条件2>2,不符合。

　　第三行是王五，数学99，那么相关子查询做的工作就是找出test表所有课程是数学的行，查询 张三，77|李四，68|王五，99

　　然后where条件score>99，没有数据，这时候外部条件2>0,符合。

　　那么就筛选出了数学最大的2个人，张三和王五。

　　

其实这里的子查询就是找出和当前行类型能匹配上的比他大的有多少，没有比他大的他就是最大

那么找top1就是 1>(xxx)，topN就是N>(xxxxx)