mysql-sql-练习题-2

日期+topN

日期
最值 topN 任意区间
- topN 每年温度top2
- - 建表
  - 排名函数
  - 万能公式（条关）
- 任意区间各科第1,3,5名
- - 排名函数
  - 万能公式

日期

本周过生日

-- 本周表示 加减日期 格式化 拼接
select *
from student
where date_format(s_age,concat(year(curdate()),date_format(s_age,'-%m-%d'))) -- 生日格式成当前年份
  between adddate(curdate(),-(date_format(curdate(),'%w') - 1)) -- 本周周一
  and
  adddate(curdate(),7 - date_format(curdate(),'%w')) -- 本周周日
;

最值 topN 任意区间

topN 每年温度top2

每一年出现过的最高气温top2及日期
在这里插入图片描述
字符串2010012325表示：2010年01月23日气温25度

建表

create table record(
	line varchar(10) not null comment '字符串'
) comment'日期温度记录表';

insert into record 
values
('2014010114'),
('2014010216'),
('2014010317'),
('2014010410'),
('2014010506'),
('2012010609'),
('2012010732'),
('2012010812'),
('2012010919'),
('2012011023'),
('2001010116'),
('2001010212'),
('2001010310'),
('2001010411'),
('2001010529'),
('2013010619'),
('2013010722'),
('2013010812'),
('2013010929'),
('2013011023'),
('2008010105'),
('2008010216'),
('2008010337'),
('2008010414'),
('2008010516'),
('2007010619'),
('2007010712'),
('2007010812'),
('2007010999'),
('2007011023'),
('2010010114'),
('2010010216'),
('2010010317'),
('2010010410'),
('2010010506'),
('2015010649'),
('2015010722'),
('2015010812'),
('2015010999'),
('2015011023');

排名函数

with tmp as(-- 截取出日期温度
  select
    substring(line,1,4) year1,
    substring(line,5,4) md,
    cast(substring(line,-2,2) as signed) temperature -- 带符号的温度
  from record
),
  tmp1 as(-- 每年温度排名
    select *,dense_rank() over(partition by year1 order by temperature desc) dr
    from tmp
  ) -- 每年最高气温top2
select * from tmp1 where dr <= 2;

在这里插入图片描述
17行

万能公式（条关）

-- 同一年：任取一行温度和所有行逐行比较
-- 没有比我大的：最大值
-- 比我大的个数1个：第二大值

with tmp as(-- 截取出日期温度
  select
    substring(line,1,4) year1,
    substring(line,5,4) md,
    cast(substring(line,-2,2) as signed) temperature -- 带符号的温度
  from record
)
select * -- 输出格式
from tmp
where (-- 过滤出需求
  select count(distinct(temperature)) -- 并列也满足需求，去重即可输出
  from tmp tmp1 -- 关联
  where tmp.year1 = tmp1.year1 and tmp.temperature < tmp1.temperature -- 逐行比较
) <= 1
order by year1,temperature desc;

任意区间各科第1,3,5名

排名函数

with tmp as(-- 各科分数排名
  select *,dense_rank() over(partition by c_id order by score desc) dr
  from score
)
select *
from tmp -- 单字段 in 单结果集
where dr in (1,3,5);

在这里插入图片描述
11行

万能公式

select *
from score sc
where (-- 过滤出各科第1,3,5名
  select count(distinct(score))
  from score sc1
  where sc.c_id = sc1.c_id and sc.score < sc1.score
) in (0,2,4)
order by c_id,score desc;