熊猫不如猫熊

分类

/ 注册

全部文章（共77篇）

题解 | #获得积分最多的人(三)#

先用窗口函数计算积分，再把积分进行排序，在连接用户表筛选 select t1.id ,t1.name ,t0.grade as grade_sum from ( select user_id,grade,rank()over(order by grade desc) as rk from ( sel...

2024-09-02

0 133

题解 | #牛客的课程订单分析(六)#

select t.id ,is_group_buy ,if(is_group_buy='Yes',NULL,client.name) from ( select * ,count(*) over (partition by user_id) as cnt...

2024-09-02

0 91

题解 | #牛客每个人最近的登录日期(四)#

新加一列注册日期，注册日期就是登入日期最小值，统计登入日期等于注册日期的行 select date ,sum(if(date=reg_date,1,0)) as new from ( select user_id ,date ,min...

2024-09-02

0 80

题解 | #获取每个部门中当前员工薪水最高的相关信息#

用row_number排序取出最大值 select dept_no ,emp_no ,salary as maxSalary from ( select dept_no,de.emp_no,salary ,row_number() over (p...

2024-09-02

1 105

题解 | employees表中为奇first_name

需要注意不改变原表顺序，所以用原表连接现在的表 select e.first_name from employees e join ( select first_name ,row_number() over (order by first_name)...

2024-09-02

0 127

题解 | #对员工薪水按照salary降序进行1-N的排名

用窗口函数进行排名编号，然后通过子查询排序 select emp_no ,salary ,t_rank from ( select emp_no ,salary ,dense_rank() over (order by...

2024-09-02

0 133

题解 | 获取薪水第二多的员工的薪水salary

select emp_no,salary from ( select * ,dense_rank() over (order by salary desc) as rk from salaries ) as a where rk = 2

2024-09-02

0 145

题解 | #查找入职员工时间排名倒数第三的员工所有信息#

select emp_no ,birth_date ,first_name ,last_name ,gender ,hire_date from ( select * ,dense_rank()over(order by hir...

2024-09-02

0 114

题解 | #找出每个学校GPA最低的同学#

select device_id ,university ,gpa from ( select * ,row_number() over (partition by university order by gpa) as r from us...

2024-09-02

0 145

题解 | #考试分数(二)#

select id,job,score from ( select id ,job ,score ,avg(score) over (partition by job) as avg_score from grade ) as a w...

2024-09-02

0 129