SQL刷题查漏补缺5

1，limit的用法
limit子句用于限制查询结果返回的数量，常用于分页查询

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

select * from student limit 2,1;  #查询第三条数据
select * from t_user limit 0,10;  # 查询10条数据，索引从0到9，第1条记录到第10条记录
select * from t_user limit 5,8;   ## 查询8条数据，索引从5到12，第6条记录到第13条记录
1
2
3
4
5
6
7

2，count(1)、count()和count(指定字段)
count(列名)：查询列名那一列的，字段为null不统计
count() ：统计所有的行数，包括为null的行
count(1) 类似count()，但效率一般比count（）高

3，SQL160

1）统计每天的点赞量和转发量-----分组后求和

select t2.tag,DATE_FORMAT(start_time, '%Y-%m-%d')  dt,sum(t1.if_like) as dz ,sum(t1.if_retweet) as gz
from tb_user_video_log t1 left join tb_video_info t2 on t1.video_id = t2.video_id
where DATE_FORMAT(start_time, '%Y-%m-%d')  between '2021-09-25' and '2021-10-03'
group by t2.tag,DATE_FORMAT(start_time, '%Y-%m-%d')
1
2
3
4

2）一周内的总点赞数和最大的转发量----窗口函数

(partition by tag order by dt desc rows between current row and 6 following )
1

3）筛选排序

总代码

select * 
from 
(select tag,dt,sum(dz) over w sum_dz_7d,max(gz) over w max_gz_7d
from
(select t2.tag,DATE_FORMAT(start_time, '%Y-%m-%d')  dt,sum(t1.if_like) as dz ,sum(t1.if_retweet) as gz
from tb_user_video_log t1 left join tb_video_info t2 on t1.video_id = t2.video_id
where DATE_FORMAT(start_time, '%Y-%m-%d')  between '2021-09-25' and '2021-10-03'
group by t2.tag,DATE_FORMAT(start_time, '%Y-%m-%d')
 ) a 
 window w as (partition by tag order by dt desc rows between current row and 6 following)
 ) b
where dt between '2021-10-01' and '2021-10-03'
order by tag desc, dt asc;
1
2
3
4
5
6
7
8
9
10
11
12
13

4,SPU 与SKU
SPU：手机 -> 苹果手机 -> iPhone 13 Pro Max
SKU： iPhone 13 Pro Max 远峰蓝 128G

5，如何确定中位数的位置
升序序号>= 总序号/2 且 降序序号>= 总序号/2
例1：
SQL270查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序

select id,job,score,rk2 
from
(select *,count(score) over(partition by job) as total,
 row_number() over(partition by job order by score) as rk1,
 row_number() over(partition by job order by score desc) as rk2
from grade) t
where rk1 >= total/2 and rk2 >= total/2
order by id
1
2
3
4
5
6
7
8

SQL282
通过abcd等级，判别中位数

例2：

select grade
from
(select grade,(select sum(number) from class_grade) as total,
sum(number)over(order by grade ) a,
sum(number) over(order by grade desc) b
from class_grade )t
where a >=total/2 and b >=total/2
1
2
3
4
5
6
7

6，自己划定类别
SQL191

select 
if(a2.author_level <=2,'1-2级',if(a2.author_level >=5,'5-6级','3-4级')) as 'level_cnt',
count(a1.author_id) as 'num'
from answer_tb a1 left join author_tb a2 using(author_id)
where a1.char_len >= 100
group by level_cnt
order by num desc ;
1
2
3
4
5
6
7

1）用if划定范围
if( 条件1,‘列名1’，if（条件2，‘列名2’，‘列名3’）)
2）在select后面定义的列名，也可以直接根据这个分组

7，要求用户在19：00时在线，筛选：

where '19:00' between date_format(in_datetime,'%H:%i') and date_format(out_datetime,'%H:%i')
1

8，当前日期： current_date

9，case when

case 列名
　　　　when   条件值1   then  选项1
　　　　when   条件值2    then  选项2.......
　　　　else     默认值      end
1
2
3
4

case  
　　　　when  列名= 条件值1   then  选项1
　　　　when  列名=条件值2    then  选项2.......
　　　　else    默认值 end
1
2
3
4