heyboysay

分类

未归档(1) 题解(2)

/ 注册

全部文章（共24篇）

题解 | 未下单用户统计

select count(*) as cnt from user_info u where not exists ( select 1 from order_log o where u.uid = o.uid )

2025-04-28

0 91

题解 | 返回顾客名称和相关订单号以及每个订单的总价

select c.cust_name, o.order_num, (os.quantity * os.item_price) as OrderTotal from Customers c join Orders o on c.cust_id = o.cust_id join...

2025-04-28

0 70

题解 | 获取employees中的first_name

select first_name from employees order by substring(first_name ,-2,2) asc

2025-04-27

0 81

题解 | 汇总各个部门当前员工的title类型的分配数目

select d.dept_no , d.dept_name , t.title , count(title) as count from departments d join dept_emp de on d.dept_no = de.dept_no join t...

2025-04-27

0 103

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

select emp_no , salary , dense_rank() over(order by salary desc) as t_rank from salaries order by t_rank asc ,emp_no asc

2025-04-27

0 87

题解 | 统计各个部门的工资记录数

select d.dept_no , d.dept_name , count(s.salary) as sum from departments d join dept_emp de on d.dept_no = de.dept_no join salaries s o...

2025-04-27

0 73

题解 | 查找所有员工的last_name和first_name以及对应的dept_name

select e.last_name , e.first_name , d.dept_name from employees e left join dept_emp de on e.emp_no = de.emp_no left join departments d o...

2025-04-27

0 74

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

select e.emp_no , s.salary , e.last_name , e.first_name from employees e inner join salaries s on e.emp_no =s.emp_no where s.salary =...

2025-04-26

0 78

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

select emp_no , salary from ( select emp_no , salary , dense_rank() over(order by salary desc) as rank_ from ...

2025-04-26

0 112

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

select t.title , round(avg(s.salary),4) as avg_salary from titles t inner join salaries s on t.emp_no = s.emp_no group by t.title order by av...

2025-04-26

0 82