烛少

分类

题解(53)

/ 注册

全部文章（共68篇）

题解 | #将employees表中的所有员工的last_name和first_name通过引号连接起来。#

select concat(last_name,"'",first_name) name from employees * 一次性的更改 alter table employees drop column name; alter table employees add column name var...

Mysql

2022-02-06

0 375

题解 | #查找在职员工自入职以来的薪水涨幅情况#

select distinct emp_no ,(first_value(salary) over w - last_value(salary) over w) as growth from salaries s where emp_no in (select emp_no from salarie...

Mysql

2022-02-06

0 235

题解 | #查找在职员工自入职以来的薪水涨幅情况#

select后使用子查询即可 select e.emp_no, ( (select s2.salary from salaries s2 where s2.emp_no=e.emp_no and s2.to_date='9999-01-01') - (select s1.sa...

Mysql

2022-02-06

0 235

题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

select e.emp_no,s3.salary,last_name,first_name from employees e join salaries s3 on e.emp_no=s3.emp_no where (select count(distinct s1.salary) from s...

Mysql

2022-02-06

0 258

题解 | #获取当前薪水第二多的员工的emp_no以及其对应的薪水salary#

子查询最简便的写法 select emp_no,salary from salaries s where (select count(distinct s1.salary) from salaries s1 where s.salary<=s1.salary )=2

Mysql

2022-02-06

0 213

题解 | #近一个月发布的视频中热度最高的top3视频#

select t1.video_id ,round( ((sum(case when timestampdiff(second,start_time,end_time)>=duration then 1 else 0 end ))/count(*)*100 +sum(if_like)*5 +c...

Mysql

2022-01-23

9 1080

题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

select author,month,fans_growth_rate, sum(follow) over(partition by author order by month) as total_fans from( select author,left(start_time,7) month,...

Mysql

2022-01-22

0 329

题解 | #连续签到领金币#

select uid,month,sum(coin) as coin from (select uid,month,rowsub,count(*) as cnt, case when mod(count(*),7)>2 then floor((count(*)/7))*15+mod(count...

Mysql

2022-01-22

0 373

题解 | #每天的日活数及新用户占比#

with a as( select uid,dt,dense_rank() over(partition by uid order by dt) as rank1 from (select uid,date(in_time) dt from tb_user_log UNION select uid,...

Mysql

2022-01-22

0 289

题解 | #统计活跃间隔对用户分级结果#

with a as ( select distinct uid,date(in_time) dt , dense_rank() over(partition by uid order by date(in_time)) rank1 from tb_user_log ), c as ( select ...

Mysql

2022-01-22

0 275