一条SQL查询访问记录表(visit_log)中某个类目(catalog_id)的访问量(visit)排前两名的记录行

时间:2023-01-18 04:34:33

原来看过一篇文章,如下

----------------------------------------------------------------------------------------------------------------

sql server查询每门课程的前两名的学生编号,课程编号,成绩并排序

 

数据结构介绍:

成绩表(编号,学号,科目编号,成绩)

SQL基础了解:

select * from 成绩表 

对于Sql Server它查询的过程是逐条查询的,也就是一条一条记录进行查询的;

那么我们可以先思考下,当它查“成绩表”时,第一条成功后才查询第二条的;

 

假设查特定的一门科目的最高分值前2条记录:

select top 2 * from 成绩表  where 科目编号=1  order by 成绩 DESC

 

 那么当要查询每一门科目成绩的前两条,就可以这样思考:

select  查询到第一条时,会得到 (编号,学号,科目编号,成绩),

那么这一条记录是否符当前成绩是当前科目最高分的前两条件呢?

上边已经处理为取前两条的方法了,那么这里进行下应该就可以了:

select 学号,科目编号,成绩 from 成绩表 AS A

where 学号 in

 (

select top 2 学号 from 成绩表 AS B  where B.科目编号= A.科目编号  order by 成绩 DESC

)

 

 上面的脚本可以理解为:

因为查询是逐条的,所以(黄色区域)可以理解为,当第一条数据获取后就进行条件判断;

学号是否存在于当前记录中科目最高成绩的前两条;

子查询部分就是当前这条记录的科目最高成绩的前两个学号

 

----------------------------------------------------------------------------------------------

 

现在遇到了一个这样的问题

一条SQL查询访问记录表(visit_log)中某个类目(catalog_id)的访问量(visit)排前两名的记录行

表结构

一条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 a
where
    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

 

结果

一条SQL查询访问记录表(visit_log)中某个类目(catalog_id)的访问量(visit)排前两名的记录行

 

最后,还有一种非常神奇的用法,如下:

select 
    id,
    catalog_id, 
    substring_index(
        group_concat(
            visit order by visit desc
        ), 
        ',', 
        2
    )      
from 
    visit_log
group by
    catalog_id

输出结果

一条SQL查询访问记录表(visit_log)中某个类目(catalog_id)的访问量(visit)排前两名的记录行