会编程的发呆爱好者加麻加辣

分类

/ 注册

全部文章（共208篇）

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

with diergao as (select max(salary)as dier from salaries where salary<(select max(salary) from salaries)) select e.emp_no,s.salary,e.last_name,e....

2025-03-15

0 32

题解 | 统计出当前各个title类型对应的员工当前薪水对应的平均工资

select title, avg(s.salary) from titles t join salaries s on t.emp_no=s.emp_no group by t.title order by avg(s.salary) 简单，考察分组

2025-03-15

0 34

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

select a.dept_no, a.emp_no,a.salary from (select d.dept_no, d.emp_no,s.salary, row_number () over(partition by d.dept_no order by s.salary desc) as ra...

2025-03-14

0 24

题解 | 获取所有员工当前的manager

select e.emp_no,m.emp_no as manager from dept_emp e join dept_manager m on e.dept_no = m.dept_no and e.emp_no != m.emp_no 如果员工本身是经理的话则不显示,翻译：e.emp_no ...

2025-03-14

0 20

题解 | 查找所有员工的last_name和first_name以及对应部门编号dept_no

select e.last_name, e.first_name, d.dept_no from employees e left join dept_emp d on e.emp_no = d.emp_no 简单，考察左连接

2025-03-14

0 35

题解 | 查找当前薪水详情以及部门编号dept_no

select s.emp_no, s.salary,s.from_date,s.to_date,d.dept_no from salaries s join dept_manager d on s.emp_no = d.emp_no order by s.emp_no 简单

2025-03-14

0 23

题解 | 浙大不同难度题目的正确率

select qq.difficult_level, count(if (q.result='right',1,null))/count(q.question_id) as correct_rate from user_profile u join question_practice_detai...

2025-03-14

0 22

题解 | 统计复旦用户8月练题情况

select u.device_id,u.university, count(q.question_id) as question_cnt, count(case when q.result ='right' then 1 else null end) as right_question_cnt f...

2025-03-13

0 25

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

select a.device_id, a.university,a.gpa from (select *,row_number()over(partition by university order by gpa asc) as ranking from user_profile)a where ...

2025-03-13

0 31

题解 | 截取出年龄

select substring(profile,12,2) as age,count(device_id) as number from user_submit group by age order by age desc substring的应用

2025-03-13

0 31