sql常用操作之限量查询

题1

https://www.nowcoder.com/practice/fbe36305c6dd4954a05cc2f2f12e4f4a?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select uid, nick_name, register_time
from user_info
order by register_time asc
limit 3

select uid, nick_name, register_time
from (
    select uid, nick_name,register_time,
           ROW_NUMBER() over(order by register_time) as ranks # 列名要重命名。列名不能是row_number，否则报错
    from user_info
) as t
where ranks <= 3;
#limit 3;

with t as(
    select uid, nick_name,register_time,
           ROW_NUMBER() over(order by register_time) as ranks # 列名要重命名。列名不能是row_number，否则报错
    from user_info
)

select uid, nick_name, register_time
from t
where ranks <= 3;
# limit 3;

题2

https://www.nowcoder.com/practice/718d36d2667b48faa2168b6c1521816a?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
先生成临时表t，表示符合条件（求职为算法、答卷为算法、注册当天完成答卷）的人的信息。
最后分类，求出每人的成绩最大值再排序。

with t as(
    select t1.uid, level, register_time, score
    from user_info t1 join examination_info t2 join exam_record t3 on t1.uid = t3.uid and t2.exam_id = t3.exam_id
    where job="算法" and tag = '算法' and date(register_time) = date(submit_time)
)

select uid, level, register_time, max(score) as max_score
from t
group by uid
order by max_score DESC
limit 6, 3

limit用法：【select * from tableName limit i,n 】;
i : 为查询结果的索引值（默认从0开始）；n : 为查询结果返回的数量

或直接写：

select t1.uid, level, register_time, max(score) as max_score
# from user_info t1 join examination_info t2 join exam_record t3 on t1.uid = t3.uid and t3.exam_id = t2.exam_id
from user_info t1 join exam_record using(uid) join examination_info using(exam_id)
where 
    job = "算法"       # job like '算法' 
    and tag = '算法'       # tag like '算法'
    and date(register_time) = date(submit_time) # 用date函数，即取年月日
#     and DATE_FORMAT(register_time, '%Y%m%d')=DATE_FORMAT(submit_time, '%Y%m%d')
group by uid
order by max_score desc
limit 6, 3  # 表示取第7~9条