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条记录
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')
2)一周内的总点赞数和最大的转发量----窗口函数
(partition by tag order by dt desc rows between current row and 6 following )
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;
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
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
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)用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')
8,当前日期: current_date
9,case when
case 列名
when 条件值1 then 选项1
when 条件值2 then 选项2.......
else 默认值 end
case
when 列名= 条件值1 then 选项1
when 列名=条件值2 then 选项2.......
else 默认值 end