原来看过一篇文章,如下
----------------------------------------------------------------------------------------------------------------
sql server查询每门课程的前两名的学生编号，课程编号，成绩并排序
数据结构介绍：
成绩表（编号，学号，科目编号，成绩）
SQL基础了解：
select * from 成绩表
对于Sql Server它查询的过程是逐条查询的，也就是一条一条记录进行查询的；
那么我们可以先思考下，当它查“成绩表”时，第一条成功后才查询第二条的；
假设查特定的一门科目的最高分值前2条记录：
select top 2 * from 成绩表 where 科目编号＝1 order by 成绩 DESC
那么当要查询每一门科目成绩的前两条，就可以这样思考：
select 查询到第一条时，会得到 （编号，学号，科目编号，成绩），
那么这一条记录是否符当前成绩是当前科目最高分的前两条件呢？
上边已经处理为取前两条的方法了，那么这里进行下应该就可以了：
select 学号，科目编号，成绩 from 成绩表 AS A
where 学号 in
(
)
上面的脚本可以理解为：
因为查询是逐条的，所以（黄色区域）可以理解为，当第一条数据获取后就进行条件判断；
学号是否存在于当前记录中科目最高成绩的前两条；
子查询部分就是当前这条记录的科目最高成绩的前两个学号
----------------------------------------------------------------------------------------------
现在遇到了一个这样的问题
一条SQL查询访问记录表(visit_log)中某个类目(catalog_id)的访问量(visit)排前两名的记录行
表结构
按照上文的写法,那么应该这样写
select id, catalog_id, visit from visit_log as a where id in ( select id from visit_log as b where a.catalog_id = b.catalog_id order by visit desc limit 2 ) as c
但是这么写是不行的, 因为mysql不支持在子查询中使用limit, 会报如下错误
14:19:12 select id, catalog_id, visit from visit_log as a where id in ( select id from visit_log as b where a.catalog_id = b.catalog_id order by visit desc limit 2 ) Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 0.000 sec
网上有说过解决这个问题的方法,有如下几个
1). 多加一个select id 到子查询中
select id, catalog_id, visit from visit_log as a where id in ( select id from ( select id from visit_log as b where a.catalog_id = b.catalog_id order by visit desc limit 2 ) as c )
报错
14:44:59 select id, catalog_id, visit from visit_log as a where id in ( select id from ( select id from visit_log as b where a.catalog_id = b.catalog_id order by visit desc limit 2 ) as c ) Error Code: 1054. Unknown column 'a.catalog_id' in 'where clause' 0.000 sec
2) 将子查询挪到from里,这种显然不行,因为我的子查询有 a.catalog 这个信息,如果移动到子查询就会变为
select id, catalog_id, visit from select id from ( select id from visit_log as b where a.catalog_id = b.catalog_id order by visit desc limit 2 ) as c
那么,我们只能避开in和exists, 使用 score 来定位这前两名, 如下
select a . *from visit_log as awhere visit >= (select visit from visit_log as b where a.catalog_id = b.catalog_id order by visit desc limit 1 , 1)order by catalog_id asc , visit desc
结果
最后,还有一种非常神奇的用法,如下:
select id, catalog_id, substring_index( group_concat( visit order by visit desc ), ',', 2 ) from visit_loggroup by catalog_id
输出结果
