gogo668

分类

/ 注册

全部文章（共63篇）

题解 | #商品交易(网易校招笔试真题)#

select distinct a.id,a.name,a.weight,a.total from (select g.id,name,weight,sum(count) over (partition by goods_id) as total from goods g join trans t ...

2024-11-06

0 41

题解 | #网易云音乐推荐(网易校招笔试真题)#

select music_name from music_likes join music on id=music_id where user_id in (select follower_id from follow where user_id=1) and music_id not in (s...

2024-11-06

0 83

题解 | #找到每个人的任务#

select person.id,name,content from person left join task on person.id = task.person_id 根据运行结果知用person表 left join task表

2024-11-05

0 34

题解 | #刷题通过的题目排名#

select id,number,dense_rank() over (order by number desc) as t_rank from passing_number 根据运行结果提示，应该选用dense_rank() 窗口函数，且排序按照number降序

2024-11-05

0 34

题解 | #出现三次以上相同积分的情况#

select number from grade group by number having count(number)>=3 重复字段计数，用group by +having函数

2024-11-05

0 55

给出employees表排名为奇数行的first_name

select e.first_name from employees e join (select first_name,row_number()over(order by first_name) as first from employees) as a on e.first_name =a.fi...

2024-11-05

0 36

题解 | #获取有奖金的员工相关信息。#

根据题目输出字段要求，需要对三张表进行链接操作，即join函数两次使用；又根据bonus的构成条件，使用case when对三种btype进行计算；最后细节处理，即用round（bonus字段，1）函数对bonus取一位小数，并且用where函数筛选当前日期并且进行升序排序。 SELECT ...

2024-10-30

0 85

使用exists查找未分配具体部门的员工的所有信息

exists的用法为，验证是否存在满足子查询的条件 select * from employees where not exists (select dept_no from dept_emp where employees.emp_no=dept_emp.emp_no )

2024-10-30

0 60

题解 | 分页查询每5行一页，返回第2页的数据#

select * from employees limit 5 offset 5; LIMIT 和 OFFSET 子句通常一起使用来进行分页查询。LIMIT 子句限制了查询结果的数量，而 OFFSET 子句则指定了从哪一条记录开始获取这些记录。

2024-10-23

0 44

题解 | #平均工资#

SELECT AVG(salary) AS avg_salary FROM salaries WHERE salary NOT IN ( SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01' ) AND salary N...

2024-10-23

0 43